﻿using AutoMapper;
using Dapper;
using DocumentFormat.OpenXml.Drawing;
using DocumentFormat.OpenXml.Drawing.Charts;
using DocumentFormat.OpenXml.Math;
using DocumentFormat.OpenXml.Office2010.CustomUI;
using DocumentFormat.OpenXml.Office2013.Drawing.ChartStyle;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml.Wordprocessing;
using housingsafety.server.Caching.Core;
using housingsafety.server.Entities;
using housingsafety.server.Entities.DataModels;
using housingsafety.server.Helpers;
using housingsafety.server.Models;
using housingsafety.server.Repository;
using housingsafety.server.Svcs;
using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Options;
using Newtonsoft.Json;
using Npgsql;
using NPOI.SS.Formula.Functions;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.IO;
using System.Linq;
using System.Text;
using System.Text.Json;

namespace housingsafety.server.Controllers
{
    /// <summary>
    /// 房屋管理服务
    /// </summary>
    public class VillageController : BaseController<HouseMngController>
    {
        public IConfiguration Configuration { get; }
        private readonly IRepository<ZmVillageFireSafety> _villageFireSafetyRepository;
        private readonly ICache _cache;
        private readonly IMapper _mapper;
        private readonly IStorageProvider _storageProvider;
        private readonly ZmDbContext _dbContext;
        private readonly IRepository<ZmUser> _userRepository;
        private readonly IRepository<ZmBuildings> _buildingsRepository;
        private readonly IRepository<ZmBuildingsLayer> _buildingsLayerRepository;
        private readonly IRepository<ZmUpdateLog> _zmupdateRepository;
        private readonly IRepository<ZmUpdateLog> _zmupdate;
        private readonly IRepository<ZmFileAttach> _fileAttachRepository;
        private readonly IRepository<ZmPlaceCommercial> _placeCommercialRepository;
        private readonly IRepository<ZmPlaceResident> _placeResidentRepository;
        private readonly IOptions<StaticDataConf> _staticVtOptions;
        private readonly IRepository<ZmDepartment> _departmentRepository;
        private readonly IRepository<RiskCensusHouseYhpc> _riskHouseRepository;
        private readonly IRepository<RiskCensusYhpcUpldFile> _riskHouseFileRepository;
        private readonly IRepository<ZmPlaceResidentCheck> _placeResidentCheckRepository;
        private readonly IRepository<ZmPlaceCommercialCheck> _placeCommercialCheckRepository;
        private readonly IRepository<ZmYtPhoto> _zmYtPhotoRepository;
        private readonly IDbTransaction _transaction;

        private readonly Microsoft.AspNetCore.Http.Features.FormOptions _defaultFormOptions = new Microsoft.AspNetCore.Http.Features.FormOptions();
        private readonly string[] _permittedExtensions = { ".mp3", ".mp4", ".jpg", ".jpeg", ".png", ".geojson", ".doc", ".docx" };

        private static object _lock = new object();

        public VillageController(IStorageProvider storageProvider
            , IRepository<ZmDepartment> departmentRepository
            , IRepository<ZmUser> userRepository
              , IRepository<ZmVillageFireSafety> villageFireSafetyRepository
              , IRepository<ZmBuildings> buildingsRepository
            , IRepository<ZmBuildingsLayer> buildingsLayerRepository
            , IRepository<ZmFileAttach> fileAttachRepository
            , IRepository<ZmPlaceCommercial> placeCommercialRepository
            , IRepository<ZmPlaceResident> placeResidentRepository
            , IRepository<RiskCensusHouseYhpc> riskHouseRepository
            , IRepository<RiskCensusYhpcUpldFile> riskHouseFileRepository
            , IRepository<ZmPlaceResidentCheck> placeResidentCheckRepository
            , IRepository<ZmPlaceCommercialCheck> placeCommercialCheckRepository
            , IRepository<ZmYtPhoto> zmYtPhotoRepository
            , IMapper mapper, ICacheProvider cacheProvider, ZmDbContext dbContext
            , IOptions<StaticDataConf> staticVtOptions
            , ILogger<HouseMngController> logger,
            IConfiguration configuration
            , IRepository<ZmSystemLog> systemLogRepository, IRepository<ZmUpdateLog> zmupdateRepository) : base(logger, systemLogRepository)
        {
            _zmupdateRepository = zmupdateRepository;
            _storageProvider = storageProvider;
            _mapper = mapper;
            _dbContext = dbContext;
            _cache = cacheProvider.CreateCache();
            _zmYtPhotoRepository = zmYtPhotoRepository;
            _userRepository = userRepository;
            _buildingsRepository = buildingsRepository;
            _buildingsLayerRepository = buildingsLayerRepository;
            _fileAttachRepository = fileAttachRepository;
            _placeCommercialRepository = placeCommercialRepository;
            _placeResidentRepository = placeResidentRepository;
            _staticVtOptions = staticVtOptions;
            _departmentRepository = departmentRepository;
            _riskHouseRepository = riskHouseRepository;
            _riskHouseFileRepository = riskHouseFileRepository;
            _placeResidentCheckRepository = placeResidentCheckRepository;
            _placeCommercialCheckRepository = placeCommercialCheckRepository;
            Configuration = configuration;
            _villageFireSafetyRepository = villageFireSafetyRepository;
            _zmupdateRepository = zmupdateRepository;

        }

        /// <summary>
        /// 检查统计分析————根据行政区划统计基本信息情况
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("village/basestatic/staticlist")]
        [ProducesResponseType(typeof(RespRet<Page<BaseInfoStaticDto>>), 200)]
        public RespRet<Page<BaseInfoStaticDto>> BaseLogList([FromQuery] PagePcztRequestInput pageRequest)
        {
            if (pageRequest.PageIndex < 0)
            {
                return new RespRet<Page<BaseInfoStaticDto>>(false, "PageIndex must >= 0");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<BaseInfoStaticDto>>(false, "user not logined");
            }

            string strWhere = " ";
            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<Page<BaseInfoStaticDto>>(false, "用户名不存在");
            }
            string district = user.District;
            var options = new JsonSerializerOptions();
            options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
            Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);


            if (model != null)
            {
                if (user.RoleType == 1 || user.RoleType == 2)
                {
                    strWhere += "and  xzq!='东城区' and xzq!='西城区'";
                }
                else if (user.RoleType == 3) //区级
                {
                    strWhere += " and xzqdm='" + model.RegionCode + "'";
                }
                else  //乡镇
                {
                    strWhere += "and xzqdm='" + model.RegionCode.Substring(0, 6) + "' ";
                }
            }



            //隐患状态
            if (!string.IsNullOrEmpty(pageRequest.Pczt + ""))
            {
                strWhere += "and pczt =" + pageRequest.Pczt + "";
            }

            //隐患来源
            if (!string.IsNullOrEmpty(pageRequest.Yhly + ""))
            {
                strWhere += "and yhly = " + pageRequest.Yhly + "";
            }




            string sql = "select ROW_NUMBER() OVER ()index ,* from jcjbxxgqview t where 1=1 " + strWhere + " order by xzqdm ";
            var list = _dbContext.Database.GetDbConnection().Query<BaseInfoStaticDto>(sql).ToList();
            int count = list.Count;

            var pageData = new Page<BaseInfoStaticDto>(list
                , pageRequest.PageIndex + 1, pageRequest.Limit, count);
            return new RespRet<Page<BaseInfoStaticDto>>(true, "Ok", pageData);
        }
        /// <summary>
        /// 自查统计分析————根据乡镇统计基本信息
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("village/basestatic/Townshipbaselist")]
        public RespRet<Page<object>> Townshipbaselist([FromQuery] string xzq, [FromQuery] int pageindex, [FromQuery] int pagesize)
        {
            if (pageindex < 0)
            {
                return new RespRet<Page<object>>(false, "PageIndex must >= 0");
            }


            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }

            string strWhere = " ";
            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<Page<object>>(false, "用户名不存在");
            }
            string district = user.District;
            var options = new JsonSerializerOptions();
            options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
            Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);


            if (model != null)
            {
                if (user.RoleType == 1 || user.RoleType == 2)
                {
                    strWhere += "and  xzq!='东城区' and xzq!='西城区'";
                }
                else if (user.RoleType == 3) //区级
                {
                    strWhere += " and xzq='" + xzq + "'";
                }
                else  //乡镇
                {
                    string towndm = model.RegionCode;
                    if (model.RegionCode.Length != 12)
                    {
                        towndm += "000";
                    }

                    strWhere += "and xzq='" + xzq + "' and towndm='" + towndm + "'";
                }
            }

            string sql = @"select ROW_NUMBER() OVER ()index ,* from jcjbxxxzview   
                           where  1 = 1 " + strWhere + " order by xzqdm  limit " + pagesize + " offset (" + pageindex + " - 1) * " + pagesize + "";
            if (sql.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            string sql_count = @"select count(1) cnt from jcjbxxxzview   where  1 = 1 " + strWhere + "";

            if (sql_count.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }


            int count = _dbContext.Database.GetDbConnection().QueryFirstOrDefault<int>(sql_count);
            var datas = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            return new RespRet<Page<object>>(true, "ok", new Page<object> { Items = datas, NextPageIndex = pageindex + 1, PageTotal = count, Total = count });

        }
        /// <summary>
        /// 自查统计分析————根据村级统计基本信息
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("village/basestatic/Villagebaselist")]
        public RespRet<Page<object>> Villagebaselist([FromQuery] string xzq, [FromQuery] string town, [FromQuery] int pageindex, [FromQuery] int pagesize)
        {
            if (pageindex < 0)
            {
                return new RespRet<Page<object>>(false, "PageIndex must >= 0");
            }
            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }
            string strWhere = "";
            if (!string.IsNullOrEmpty(xzq + ""))
            {
                strWhere += "and xzq ='" + xzq + "'";
            }
            if (!string.IsNullOrEmpty(town + ""))
            {
                strWhere += "and town ='" + town + "'";
            }
            string sql = @"select ROW_NUMBER() OVER ()index ,* from jcjbxxcjview 
where 1=1 " + strWhere + " order by xzqdm limit " + pagesize + " offset (" + pageindex + " - 1) * " + pagesize + "";

            if (sql.Contains("insert into", StringComparison.OrdinalIgnoreCase)
               || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
               || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            string sql_count = @"select count(1) from jcjbxxcjview  where 1=1 " + strWhere + "";
            if (sql_count.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }


            int count = _dbContext.Database.GetDbConnection().QueryFirstOrDefault<int>(sql_count);
            var datas = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            return new RespRet<Page<object>>(true, "ok", new Page<object> { Items = datas, NextPageIndex = pageindex + 1, PageTotal = count, Total = count });


        }

        /// <summary>
        /// 自查统计分析————根据行政区划统计隐患情况
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("village/yhstatic/staticlist")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<Page<object>>), 200)]
        public RespRet<Page<object>> YhStaticList([FromQuery] PagePcztRequestInput pageRequest)
        {
            if (pageRequest.PageIndex < 0)
            {
                return new RespRet<Page<object>>(false, "PageIndex must >= 0");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }


            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<Page<object>>(false, "用户名不存在");
            }
            string district = user.District;

            var options = new JsonSerializerOptions();
            options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
            Xzq xzq = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);


            string strWhere = "  ";

            if (user.RoleType == 1 || user.RoleType == 2)
            {
                strWhere += " and xzq!='东城区' and xzq!='西城区' ";
            }
            else if (user.RoleType == 3)
            {
                strWhere += " and xzq='" + xzq.RegionName + "'";
            }
            else
            {
                string xzqdm = xzq.RegionCode.Substring(0, 6);
                strWhere += " and xzqdm='" + xzqdm + "'";
            }

            string sql = @"select ROW_NUMBER() OVER ()index ,* from jcyhgqview  where 1=1 " + strWhere + " order by xzqdm ";
            var list = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            int count = list.Count;

            var pageData = new Page<object>(list
                , pageRequest.PageIndex + 1, pageRequest.Limit, count);
            return new RespRet<Page<object>>(true, "Ok", pageData);
        }


        /// <summary>
        /// 自查统计分析————根据乡镇统计隐患情况
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("village/yhstatic/TownshipYhstaticlist")]
        public RespRet<Page<object>> TownshipYhstaticlist([FromQuery] string xzq, [FromQuery] int pageindex, [FromQuery] int pagesize)
        {
            if (pageindex < 0)
            {
                return new RespRet<Page<object>>(false, "PageIndex must >= 0");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }
            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<Page<object>>(false, "用户名不存在");
            }
            string district = user.District;
            var options = new JsonSerializerOptions();
            options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
            Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);

            string strWhere = "";
            if (!string.IsNullOrEmpty(xzq))
            {
                if (user.RoleType == 1 || user.RoleType == 2)
                {
                    strWhere += "and xzq ='" + xzq + "'";
                }
                else if (user.RoleType == 3)
                {
                    strWhere += " and xzq='" + model.RegionName + "'";
                }
                else
                {
                    strWhere += " and towndm='" + model.RegionCode + "'";
                }
            }

            string sql = "select ROW_NUMBER() OVER ()index ,* from jcyhxzview  " +
                "where  1=1 " + strWhere + " limit " + pagesize + " offset (" + pageindex + " - 1) * " + pagesize + "";
            if (sql.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            string sql_count = "select count(1) cnt from zcyhxzview  where  1=1 " + strWhere + "";
            if (sql_count.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }


            int count = _dbContext.Database.GetDbConnection().QueryFirstOrDefault<int>(sql_count);
            var datas = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            return new RespRet<Page<object>>(true, "ok", new Page<object> { Items = datas, NextPageIndex = pageindex + 1, PageTotal = count, Total = count });

        }
        /// <summary>
        /// 自查统计分析————根据村级统计隐患情况
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("village/yhstatic/VillageYhstaticlist")]
        public RespRet<Page<object>> VillageYhstaticlist([FromQuery] string xzq, [FromQuery] string town, [FromQuery] int pageindex, [FromQuery] int pagesize)
        {
            if (pageindex < 0)
            {
                return new RespRet<Page<object>>(false, "PageIndex must >= 0");
            }
            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }
            string strWhere = "";
            if (!string.IsNullOrEmpty(xzq + ""))
            {
                strWhere += "and xzq ='" + xzq + "'";
            }
            if (!string.IsNullOrEmpty(town + ""))
            {
                strWhere += "and town ='" + town + "'";
            }
            string sql = "select ROW_NUMBER() OVER ()index ,* from jcyhcjview where 1=1 " + strWhere + " order by xzqdm limit " + pagesize + " offset (" + pageindex + " - 1) * " + pagesize + "";

            if (sql.Contains("insert into", StringComparison.OrdinalIgnoreCase)
               || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
               || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            string sql_count = "select count(1) cnt from zcyhcjview  where 1=1 " + strWhere + " ";

            if (sql_count.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }


            int count = _dbContext.Database.GetDbConnection().QueryFirstOrDefault<int>(sql_count);
            var datas = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            return new RespRet<Page<object>>(true, "ok", new Page<object> { Items = datas, NextPageIndex = pageindex + 1, PageTotal = count, Total = count });


        }
        /// <summary>
        /// 根据某个隐患项获取此隐患的房屋
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("village/yhstatic/yhdetaile")]
        public RespRet<Page<object>> yhdetaile([FromQuery] string check_id, [FromQuery] int pageindex, [FromQuery] int pagesize, [FromQuery] string xzqdm, [FromQuery] string towndm, [FromQuery] string villagedm, [FromQuery] string type)
        {
            if (pageindex < 0)
            {
                return new RespRet<Page<object>>(false, "PageIndex must >= 0");
            }
            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }
            string strWhere = "";
            if (!string.IsNullOrEmpty(xzqdm))
            {
                strWhere += " and xzqdm='" + xzqdm + "'";
            }
            if (!string.IsNullOrEmpty(towndm))
            {
                strWhere += " and towndm='" + towndm + "'";
            }
            if (!string.IsNullOrEmpty(villagedm))
            {
                strWhere += " and villagedm='" + villagedm + "'";
            }
            if (!string.IsNullOrEmpty(check_id))
            {
                if (check_id != "yhtotal" && check_id != "zgtotal" && check_id != "sytotal")
                {
                    strWhere += " and check_id='" + check_id + "'";
                }
            }
            string sql = "";
            string sql_count = "";
            if (type == "sy")
            {
                sql = "select ROW_NUMBER() OVER ()index ,* from (select m1.*,hzxm,address,'未整改' zgzt from (select * from (select t1.*,fwzt from zm_pc_yhlog_dx_cj t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh) b1 where fwzt is null) m1 left join zm_pc_base_cj t3 on m1.fwbh=t3.fwbh where zg_value='0' order by m1.towndm, m1.villagedm) t where 1=1  " +
                   " " + strWhere + " limit " + pagesize + " offset (" + pageindex + " - 1) * " + pagesize + "";


                sql_count = "select count(1) cnt from (select m1.*,hzxm,address from (select * from (select t1.*,fwzt from zm_pc_yhlog_dx_cj t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh) b1 where fwzt is null) m1 left join zm_pc_base_cj t3 on m1.fwbh=t3.fwbh where zg_value='0' order by m1.towndm, m1.villagedm) t where 1=1   " +
                   "" + strWhere;
            }
            else if (type == "zg")
            {
                sql = "select ROW_NUMBER() OVER ()index ,* from (select m1.*,hzxm,address,'已整改' zgzt from (select * from (select t1.*,fwzt from zm_pc_yhlog_dx_cj t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh) b1 where fwzt is null) m1 left join zm_pc_base_cj t3 on m1.fwbh=t3.fwbh where zg_value='1' order by m1.towndm, m1.villagedm) t where 1=1  " +
                   " " + strWhere + " limit " + pagesize + " offset (" + pageindex + " - 1) * " + pagesize + "";


                sql_count = "select count(1) cnt from (select m1.*,hzxm,address from (select * from (select t1.*,fwzt from zm_pc_yhlog_dx_cj t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh) b1 where fwzt is null) m1 left join zm_pc_base_cj t3 on m1.fwbh=t3.fwbh where zg_value='1' order by m1.towndm, m1.villagedm) t where 1=1   " +
                   "" + strWhere;
            }
            else if (type == "yh")
            {
                sql = "select ROW_NUMBER() OVER ()index ,* from (select m1.*,hzxm,address,case when zg_value=1 then '已整改' else '未整改' end zgzt  from (select * from (select t1.*,fwzt from zm_pc_yhlog_dx_cj t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh) b1 where fwzt is null) m1 left join zm_pc_base_cj t3 on m1.fwbh=t3.fwbh where 1=1  order by m1.towndm, m1.villagedm) t where 1=1  " +
                   " " + strWhere + " limit " + pagesize + " offset (" + pageindex + " - 1) * " + pagesize + "";


                sql_count = "select count(1) cnt from (select m1.*,hzxm,address,case when zg_value=1 then '已整改' else '未整改' end zgzt from (select * from (select t1.*,fwzt from zm_pc_yhlog_dx_cj t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh) b1 where fwzt is null) m1 left join zm_pc_base_cj t3 on m1.fwbh=t3.fwbh where 1=1 order by m1.towndm, m1.villagedm) t where 1=1   " +
                   "" + strWhere;
            }

            if (sql_count.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            if (sql.Contains("insert into", StringComparison.OrdinalIgnoreCase)
              || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
              || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }

            int count = _dbContext.Database.GetDbConnection().QueryFirstOrDefault<int>(sql_count);
            var datas = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            return new RespRet<Page<object>>(true, "ok", new Page<object> { Items = datas, NextPageIndex = pageindex + 1, PageTotal = count, Total = count });
        }




        /// <summary>
        /// 获取当前隐患的房屋基本信息
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("village/yhhourse/detail")]
        [ProducesResponseType(typeof(RespRet<HourseBaseInfo>), 200)]
        public RespRet<HourseBaseInfo> getYhhourseDetail([FromQuery] string id)
        {
            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<HourseBaseInfo>(false, "user not logined");
            }

            HourseBaseInfo model = new HourseBaseInfo();
            string sql = "  select *,case when (coalesce(yhnum,0) >0) then  '已排查有隐患' else '已排查无隐患'  end AS pczt from " +
                "( select b1.*,yhnum from (select t1.id,t2.id yhlog_id,t1.fwbh,t1.yhly,t1.xzqdm,t1.town,t1.village,address,yhfwbh,hzxm,jzrks,zzrs,cs,fjsl,jzfjs,jzscxyaqcksl," +
                "jzxyssltsl,jzscmj,ckfhwggptzlsl,bdfwjzrydddzxcsl,mhqsl,fytsmz,xggdjsbp,jzsyjyplmhxts,jzszlwxdlgybjqs,wdwhjgfjgqsyyrkrgbdj,sstdsyyrkrclzx," +
                "gryssdcsszblyyqksddp,case when cjzwqtjyyt =0 then '否' else cjzwqtjyytmsg end cjzwqtjyytmsg,case when cs=1 and jzscxyaqcksl>=2 then '已达标'  else case when cs>=2 and jzscxyaqcksl>=2  and jzxyssltsl >=2 then '已达标' else sstdjsqk end  end sstdjsqk,jsrysstd,to_char(t1.create_on::timestamp(0) without time zone, 'YYYY-MM-DD HH24:MI:SS') pcsj,cjzwqtjyyt,use_electric_bicycle,use_bottled_liquefied_gas,use_stacked_items," +
                "mhqwbcwhyx,jyplmhxtwbcwhyx,ygbjwfwhyx,use_electric_bicycle_photo,use_bottled_liquefied_gas_photo,use_stacked_items_photo,mhqwbcwhyx_photo," +
                "jyplmhxtwbcwhyx_photo,ygbjwfwhyx_photo,use_electric_bicycle_msg,use_bottled_liquefied_gas_msg,use_stacked_items_msg,mhqwbcwhyx_msg," +
                "jyplmhxtwbcwhyx_msg,ygbjwfwhyx_msg,use_electric_bicycle_zgqk,use_bottled_liquefied_gas_zgqk,use_stacked_items_zgqk,mhqwbcwhyx_zgqk," +
                "jyplmhxtwbcwhyx_zgqk,ygbjwfwhyx_msg_zgqk, use_electric_bicycle_zgphoto,use_bottled_liquefied_gas_zgphoto,use_stacked_items_zgphoto," +
                "mhqwbcwhyx_zgphoto,jyplmhxtwbcwhyx_zgphoto,ygbjwfwhyx_msg_zgphoto,slljdx,slljdx_photo, slljdx_msg,slljdx_zgqk,slljdx_zgphoto,zxqlddcsl," +
                "zxqlyhsyqsl,sign,notes from zm_pc_baselog_cj t1, zm_pc_yhlog_cj t2 where t1.id = t2.base_id ) b1 left join (select fwbh,base_id,COALESCE(yhnum,0) yhnum, " +
                "COALESCE(zgnum,0) zgnum from  zm_pc_yhlog_cj t1 ,  (select count(fwbh) yhnum,yhlog_id,sum(case when zg_value='1' then 1 else 0 end) zgnum " +
                "from zm_pc_yhlog_dx_cj  group by yhlog_id) t2 where t1.id=t2.yhlog_id) b2  on b1.id=b2.base_id) t  where yhlog_id ='" + id + "'";

            var item = _dbContext.Database.GetDbConnection().Query<BuildingPcYhLogDetailDto>(sql).ToList();
            if (item != null && item.Count == 1)
            {
                var hourse_item = item[0];

                //use_electric_bicycle_photo_list
                //    use_bottled_liquefied_gas_photo_list
                //     {
                //    get; s
                //     { get; set; }//
                //     {
                //        get; set
                //    ygbjwfwhyx_photo_list { get; set; }//
                List<photoList> photoList = new List<photoList>();
                photoList photo = new photoList();
                if (!string.IsNullOrEmpty(hourse_item.use_electric_bicycle_photo))
                {
                    if (hourse_item.use_electric_bicycle_photo.Contains("http"))
                    {
                        string vReplace = hourse_item.use_electric_bicycle_photo.Replace("[\"", "").Replace("\"]", "");
                        photo.use_electric_bicycle_photo_list = vReplace.Split(',');

                    }
                }


                if (!string.IsNullOrEmpty(hourse_item.use_bottled_liquefied_gas_photo))
                {
                    if (hourse_item.use_bottled_liquefied_gas_photo.Contains("http"))
                    {
                        string vReplace = hourse_item.use_bottled_liquefied_gas_photo.Replace("[\"", "").Replace("\"]", "");
                        photo.use_bottled_liquefied_gas_photo_list = vReplace.Split(',');

                    }
                }

                if (!string.IsNullOrEmpty(hourse_item.use_stacked_items_photo))
                {
                    if (hourse_item.use_stacked_items_photo.Contains("http"))
                    {
                        string vReplace = hourse_item.use_stacked_items_photo.Replace("[\"", "").Replace("\"]", "");
                        photo.use_stacked_items_photo_list = vReplace.Split(',');

                    }
                }

                if (!string.IsNullOrEmpty(hourse_item.mhqwbcwhyx_photo))
                {
                    if (hourse_item.mhqwbcwhyx_photo.Contains("http"))
                    {
                        string vReplace = "";
                        vReplace = hourse_item.mhqwbcwhyx_photo.Replace("[\"", "").Replace("\"]", "");
                        photo.mhqwbcwhyx_photo_list = vReplace.Split(',');

                    }
                }

                if (!string.IsNullOrEmpty(hourse_item.jyplmhxtwbcwhyx_photo))
                {
                    if (hourse_item.jyplmhxtwbcwhyx_photo.Contains("http"))
                    {
                        string vReplace = hourse_item.jyplmhxtwbcwhyx_photo.Replace("[\"", "").Replace("\"]", "");
                        photo.jyplmhxtwbcwhyx_photo_list = vReplace.Split(',');

                    }
                }
                if (!string.IsNullOrEmpty(hourse_item.ygbjwfwhyx_photo))
                {
                    if (hourse_item.ygbjwfwhyx_photo.Contains("http"))
                    {
                        string vReplace = hourse_item.ygbjwfwhyx_photo.Replace("[\"", "").Replace("\"]", "");
                        photo.ygbjwfwhyx_photo_list = vReplace.Split(',');

                    }
                }

                photoList.Add(photo);

                model.photoList = photoList;
            }


            model.BuildingPcYhLogDetailDtoList = item;
            ///整改
            string sql_rectification = @"  select *from (select m1.*,check_id,check_name,zg_zp,zg_time, zg_name   from 
                    (select t1.base_id,t1.fwbh,t2.id dx_id,t1.id yhlog_id from zm_pc_yhlog_cj t1 left join zm_pc_yhlog_dx_cj t2 on t1.id=t2.yhlog_id ) m1 ,
                    zm_pc_yhlog_dxzgson_cj m2  where m1.dx_id=m2.yhlog_dx_id ) t where yhlog_id='" + id + "'";

            var itemLIst = _dbContext.Database.GetDbConnection().Query<RectificationYh>(sql_rectification).ToList();

            model.RectificationYhList = itemLIst;


            return new RespRet<HourseBaseInfo>(true, "Ok", model);
        }


        /// <summary>
        /// 自查统计分析————根据行政区划统计进展情况
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("village/evolvestatic/staticlist")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<Page<BaseInfoStaticDto>>), 200)]
        public RespRet<Page<BuildingPcProgree>> EvolvestaticList([FromQuery] PageProgressRequestInput pageRequest)
        {
            if (pageRequest.PageIndex < 0)
            {
                return new RespRet<Page<BuildingPcProgree>>(false, "PageIndex must >= 0");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<BuildingPcProgree>>(false, "user not logined");
            }
            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<Page<BuildingPcProgree>>(false, "用户名不存在");
            }
            string district = user.District;

            var options = new JsonSerializerOptions();
            options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
            Xzq xzq = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);


            string strWhere = "  ";
            if (user.RoleType == 1 || user.RoleType == 2)
            {
                strWhere += " and xzq!='东城区' and xzq!='西城区' ";
            }
            else if (user.RoleType == 3)
            {
                strWhere += " and xzq='" + xzq.RegionName + "'";
            }
            else
            {
                string xzqdm = xzq.RegionCode.Substring(0, 6);
                strWhere += " and xzqdm='" + xzqdm + "'";
            }
            string toDay = DateTime.Now.ToString("yyyy-MM-dd");
            string table_fwzt = "";
            if (!string.IsNullOrEmpty(pageRequest.endTime))
            {
                DateTime endData = Convert.ToDateTime(pageRequest.endTime);
                table_fwzt = "zm_building_fwzt_" + endData.Year;
            }
            else
            {
                DateTime endData = Convert.ToDateTime(toDay);
                table_fwzt = "zm_building_fwzt_" + endData.Year;
            }
            string sql = "select * from jcqkgqtext('" + pageRequest.startTime + "','" + pageRequest.endTime + " 23:59:59','" + table_fwzt + "') where 1=1 " + strWhere + " order by xzqdm";
            if (pageRequest.cked == "true")
            {

            }
            var list = _dbContext.Database.GetDbConnection().Query<BuildingPcProgree>(sql).ToList();
            int count = list.Count;

            var pageData = new Page<BuildingPcProgree>(list
                , pageRequest.PageIndex + 1, pageRequest.Limit, count);
            return new RespRet<Page<BuildingPcProgree>>(true, "Ok", pageData);
        }

        /// <summary>
        /// 自查统计分析————根据乡镇统计进展情况
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("village/evolvestatic/Townshipstaticlist")]
        public RespRet<Page<object>> Townshipstaticlist([FromQuery] string xzqdm, [FromQuery] int pageindex, [FromQuery] int pagesize, [FromQuery] string startTime, [FromQuery] string endTime)
        {
            if (pageindex < 0)
            {
                return new RespRet<Page<object>>(false, "PageIndex must >= 0");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }
            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<Page<object>>(false, "用户名不存在");
            }
            string district = user.District;

            var options = new JsonSerializerOptions();
            options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
            Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);

            string strWhere = "";

            if (user.RoleType == 3)
            {
                strWhere += " and xzq='" + model.RegionName + "'";
            }
            if (!string.IsNullOrEmpty(xzqdm))
            {
                strWhere += " and xzqdm='" + xzqdm + "'";
            }
            if (string.IsNullOrEmpty(startTime) && string.IsNullOrEmpty(endTime))
            {
                startTime = DateTime.Now.ToShortDateString();
                endTime = DateTime.Now.ToShortDateString();
            }
            string toDay = DateTime.Now.ToString("yyyy-MM-dd");
            string table_fwzt = "";
            if (!string.IsNullOrEmpty(endTime))
            {
                DateTime endData = Convert.ToDateTime(endTime);
                table_fwzt = "zm_building_fwzt_" + endData.Year;
            }
            else
            {
                DateTime endData = Convert.ToDateTime(toDay);
                table_fwzt = "zm_building_fwzt_" + endData.Year;
            }

            string sql = "select ROW_NUMBER() OVER ()index,*,case when yhcount=0 and zgcount=0 then '100.00%' else  concat( CASE\r\n    " +
            "        WHEN (round((((zgcount)::numeric / yhcount) * (100)::numeric), 2) > (100)::numeric)" +
            " THEN 100.00\r\n            ELSE round((((zgcount)::numeric / yhcount) * (100)::numeric), 2)  END, '%') end zgl " +
            " from jcqkxztext('" + startTime + "','" + endTime + " 23:59:59','','" + table_fwzt + "') where 1 = 1   " + strWhere
            + " order by xzqdm,towndm asc ";
            if (sql.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            string sql_count = "select count(1) cnt from  zcqkxztext('" + startTime + "','" + endTime + "','','" + table_fwzt + "') where 1 = 1   " + strWhere + " ";
            if (sql_count.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }


            int count = _dbContext.Database.GetDbConnection().QueryFirstOrDefault<int>(sql_count);
            var datas = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            return new RespRet<Page<object>>(true, "ok", new Page<object> { Items = datas, NextPageIndex = pageindex + 1, PageTotal = count, Total = count });

        }
        /// <summary>
        /// 自查统计分析————根据村级统计进展情况
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("village/evolvestatic/Villagestaticlist")]
        public RespRet<Page<object>> Villagestaticlist([FromQuery] string xzqdm, [FromQuery] string towndm, [FromQuery] int pageindex, [FromQuery] int pagesize, [FromQuery] string startTime, [FromQuery] string endTime)
        {
            if (pageindex < 0)
            {
                return new RespRet<Page<object>>(false, "PageIndex must >= 0");
            }
            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }

            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<Page<object>>(false, "用户名不存在");
            }


            string strWhere = "";
            if (!string.IsNullOrEmpty(xzqdm + ""))
            {
                strWhere += "and xzqdm ='" + xzqdm + "'";
            }
            if (!string.IsNullOrEmpty(towndm + ""))
            {
                strWhere += "and towndm ='" + towndm + "'";
            }
            if (string.IsNullOrEmpty(startTime) && string.IsNullOrEmpty(endTime))
            {
                startTime = DateTime.Now.ToShortDateString();
                endTime = DateTime.Now.ToShortDateString();
            }
            string toDay = DateTime.Now.ToString("yyyy-MM-dd");
            string table_fwzt = "";
            if (!string.IsNullOrEmpty(endTime))
            {
                DateTime endData = Convert.ToDateTime(endTime);
                table_fwzt = "zm_building_fwzt_" + endData.Year;
            }
            else
            {
                DateTime endData = Convert.ToDateTime(toDay);
                table_fwzt = "zm_building_fwzt_" + endData.Year;
            }
            string sql = "select ROW_NUMBER() OVER ()index ,* ,case when yhcount=0 and zgcount=0 then '100.00%' else  " +
                "concat(\r\n        CASE\r\n            WHEN (round((((zgcount)::numeric / yhcount) * (100)::numeric), 2) > (100)::numeric) THEN 100.00\r\n    " +
                "        ELSE round((((zgcount)::numeric / yhcount) * (100)::numeric), 2)\r\n        END, '%') " +
                "end zgl from jcqkcjtext('" + startTime + "','" + endTime + " 23:59:59','','" + table_fwzt + "')  where 1=1 " + strWhere + "" +
                " order by xzqdm,towndm asc ,villagedm asc";

            if (sql.Contains("insert into", StringComparison.OrdinalIgnoreCase)
               || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
               || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            string sql_count = "select count(1) cnt from zcqkcjtext('" + startTime + "','" + endTime + "')  where 1=1 " + strWhere + " ";
            if (sql_count.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            int count = _dbContext.Database.GetDbConnection().QueryFirstOrDefault<int>(sql_count);
            var datas = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            return new RespRet<Page<object>>(true, "ok", new Page<object> { Items = datas, NextPageIndex = pageindex + 1, PageTotal = count, Total = count });
        }


        /// <summary>
        /// 村级工作进展统计
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("village/evolvestatic/villagePostStatic")]
        public RespRet<Page<object>> villagePostStatic([FromQuery] int pageIndex, [FromQuery] int limit, [FromQuery] string districts, [FromQuery] string xzqdm, [FromQuery] string towndm, [FromQuery] string villagedm, [FromQuery] string startTime, [FromQuery] string endTime, [FromQuery] string cked)
        {
            if (pageIndex < 0)
            {
                return new RespRet<Page<object>>(false, "PageIndex must >= 0");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }

            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<Page<object>>(false, "用户名不存在");
            }

            var sbSql = new StringBuilder();
            var sbSqlWhere = new StringBuilder();
            var sqlParams = new List<NpgsqlParameter>();

            var sbSqlBuildingEx = new StringBuilder();

            string district = user.District;

            var options = new JsonSerializerOptions();
            options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
            Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);


            string strWhere = "  ";

            if (user.RoleType == 1 || user.RoleType == 2)
            {
                strWhere += " and xzq!='东城区' and xzq!='西城区' ";
            }
            else if (user.RoleType == 3)  //区级
            {
                strWhere += " and xzqdm='" + model.RegionCode + "'";
            }
            else if (user.RoleType == 4)
            {
                string tdm = model.RegionCode;
                if (model.RegionCode.Length != 12)
                {
                    tdm = model.RegionCode + "000";
                }
                strWhere += " and towndm='" + tdm + "'";
            }
            else if (user.RoleType == 5)
            {
                strWhere += " and villagedm='" + model.RegionCode + "'";
            }
            if (!string.IsNullOrEmpty(xzqdm))
            {
                strWhere += " and xzqdm='" + xzqdm + "'";
            }
            if (!string.IsNullOrEmpty(towndm))
            {
                strWhere += " and towndm='" + towndm + "'";
            }
            if (!string.IsNullOrEmpty(villagedm))
            {
                strWhere += " and villagedm='" + villagedm + "'";
            }



            //行政区划
            if (!string.IsNullOrEmpty(districts))
            {
                var districtCodeWhere = Utilitys.GetLikeSqlWhere(JsonConvert.SerializeObject(districts), "");
                if (!string.IsNullOrEmpty(districtCodeWhere))
                {
                    strWhere += " and  " + districtCodeWhere;
                }
            }
            string toDay = DateTime.Now.ToString("yyyy-MM-dd");
            string table_fwzt = "";
            if (!string.IsNullOrEmpty(endTime))
            {
                DateTime endData = Convert.ToDateTime(endTime);
                table_fwzt = "zm_building_fwzt_" + endData.Year;
            }
            else
            {
                DateTime endData = Convert.ToDateTime(toDay);
                table_fwzt = "zm_building_fwzt_" + endData.Year;
            }
            string sql = @"select ROW_NUMBER() OVER ()index,*,case when yhcount=0 and zgcount=0 then '100.00%' else  concat(
        CASE
            WHEN (round((((zgcount)::numeric / yhcount) * (100)::numeric), 2) > (100)::numeric) THEN 100.00
            ELSE round((((zgcount)::numeric / yhcount) * (100)::numeric), 2)
        END, '%') end zgl from jcqkcjtext('" + startTime + "','" + endTime + " 23:59:59','','" + table_fwzt+"') where 1 = 1 and xzq!= '东城区' and xzq!= '西城区'  " + strWhere + "  order by xzqdm,towndm asc ,villagedm asc limit " + limit + " offset (" + pageIndex + ") * " + limit + "";
            string sql_count = @"select count(1) cnt from jcqkcjtext('" + startTime + "','" + endTime + " 23:59:59','','" + table_fwzt + "') where 1 = 1 and xzq!= '东城区' and xzq!= '西城区'  " + strWhere; 

            if (cked == "true")
            {
                sql = @"select ROW_NUMBER() OVER ()index,*  from (select t1.xzq,t1.townold town,t1.villageold village,COALESCE(zcount, 0) zcount,COALESCE(jccount, 0) jccount,COALESCE(wjccount, 0) wjccount,COALESCE(jcl::text
, '0%') jcl,COALESCE(yhcount, 0) yhcount,COALESCE(zgcount, 0) zgcount,case when yhcount=0 and zgcount=0 then '100.00%' else  concat(
        CASE
            WHEN (round((((zgcount)::numeric / yhcount) * (100)::numeric), 2) > (100)::numeric) THEN 100.00
            ELSE round((((zgcount)::numeric / yhcount) * (100)::numeric), 2)
        END, '%') end zgl,COALESCE(syyhcount, 0) syyhcount,t2.xzqdm,t2.towndm,t1.villagedm from zdcmd t1 left join  jcqkcjtext('" + startTime + "','" + endTime + " 23:59:59','','" + table_fwzt + "') t2 on t1.villagedm=t2.villagedm) t where 1 = 1 and xzq!= '东城区' and xzq!= '西城区'  " + strWhere + "  order by xzqdm,towndm asc ,villagedm asc limit " + limit + " offset (" + pageIndex + ") * " + limit + "";
                sql_count = @"select count(1) cnt from (select t1.xzq,t1.townold town,t1.villageold village,zcount,jccount,wjccount,jcl,yhcount,zgcount,syyhcount,t2.xzqdm,t2.towndm,t1.villagedm from zdcmd t1 left join  jcqkcjtext('" + startTime + "','" + endTime + " 23:59:59','','" + table_fwzt + "') t2 on t1.villagedm=t2.villagedm) t where 1 = 1 and xzq!= '东城区' and xzq!= '西城区'  " + strWhere + " ";
            }

            if (sql.Contains("insert into", StringComparison.OrdinalIgnoreCase)
           || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
           || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }

            if (sql_count.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            //执行语句
            var list = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            int count = _dbContext.Database.GetDbConnection().QueryFirstOrDefault<int>(sql_count);

            var pageData = new Page<object>(list
                , pageIndex + 1, limit, count);
            return new RespRet<Page<object>>(true, "ok", new Page<object> { Items = list, NextPageIndex = pageIndex + 1, PageTotal = count, Total = count });

        }
        /// <summary>
        /// 检查明细
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("village/evolvestatic/getJcHourseMxDetail")]
        public RespRet<Page<object>> getHcHourseMxDetail([FromQuery] string xzqdm, [FromQuery] string towndm, [FromQuery] string villagedm, [FromQuery] string category, [FromQuery] int pageIndex, [FromQuery] int pageSize, [FromQuery] string endTime, [FromQuery] string startTime, [FromQuery] string txtKeyWord, [FromQuery] string cs, [FromQuery] string role_type)
        {
            if (pageIndex < 0)
            {
                return new RespRet<Page<object>>(false, "PageIndex must >= 0");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }

            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<Page<object>>(false, "用户名不存在");
            }

            var sbSql = new StringBuilder();
            var sbSqlWhere = new StringBuilder();
            var sqlParams = new List<NpgsqlParameter>();

            var sbSqlBuildingEx = new StringBuilder();

            string district = user.District;

            var options = new JsonSerializerOptions();
            options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
            Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);

            string strWhere = " ";
            string cj_strWhere = " ";
            if (!string.IsNullOrEmpty(txtKeyWord))
            {
                strWhere += " and (hzxm like '%" + txtKeyWord + "%' or fwbh like '%" + txtKeyWord + "%' or address like '%" + txtKeyWord + "%')";
            }

            if (!string.IsNullOrEmpty(xzqdm))
            {
                strWhere += " and xzqdm='" + xzqdm + "' ";
                cj_strWhere += " and t1.xzqdm='" + xzqdm + "' ";
            }
            if (!string.IsNullOrEmpty(towndm))
            {
                strWhere += "  and towndm='" + towndm + "' ";
                cj_strWhere += " and t1.town='" + towndm + "' ";
            }
            if (!string.IsNullOrEmpty(villagedm))
            {
                strWhere += " and villagedm='" + villagedm + "'";
                cj_strWhere += " and t1.village='" + villagedm + "' ";
            }
            if (string.IsNullOrEmpty(startTime) && !string.IsNullOrEmpty(endTime))
            {
                startTime = "2024-01-01";
                endTime = DateTime.Now.ToString("yyyy-MM-dd");
            }
            string sql = " ";
            string today = DateTime.Now.ToString("yyyy-MM-dd");
            string table_fwzt = "";
            if (!string.IsNullOrEmpty(endTime))
            {
                DateTime endData = Convert.ToDateTime(endTime);
                table_fwzt = "zm_building_fwzt_" + endData.Year;
            }
            else
            {
                DateTime endData = Convert.ToDateTime(today);
                table_fwzt = "zm_building_fwzt_" + endData.Year;
            }
            if (!string.IsNullOrEmpty(cs))
            {
                if (cs.Contains("3"))
                {
                    string[] arrr = cs.Split(',');
                    if (arrr.Length == 2)
                    {
                        string cssql = "";
                        for (int i = 0; i < arrr.Length; i++)
                        {
                            if (arrr[i] == "3")
                            {
                                cssql += "  cs >=3 OR ";
                            }
                            if (arrr[i] == "1")
                            {
                                cssql += "  cs=1 OR ";
                            }
                            if (arrr[i] == "2")
                            {
                                cssql += " cs =2 OR ";
                            }
                        }

                        strWhere += " and (" + (cssql.Substring(0, cssql.Length - 3)) + ")";
                    }
                    else
                    {
                        strWhere += " and cs >=" + cs;
                    }
                }
                else
                {
                    strWhere += " and cs in(" + cs + ")";
                }
            }
            DateTime start = Convert.ToDateTime(startTime);
            DateTime end = Convert.ToDateTime(endTime);
            if (category == "yjc")
            {

                sql = @"select ROW_NUMBER() OVER ()index ,* from (select ROW_NUMBER() OVER(PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,t1.xzq,t1.xzqdm,t1.town towndm,t1.towndm town,t1.villagedm village,t1.village villagedm,t1.hzxm,t1.address,t1.fwbh,
t1.create_on from zm_pc_baselog_cj t1,zm_pc_yhlog_cj t2 where t1.id=t2.base_id and t1.create_on BETWEEN '" + startTime + "' and '" + endTime + " 23:59:59') t1 " +
         "    where  create_on BETWEEN '" + startTime + "' and '" + endTime + " 23:59:59' and row_num=1  and fwbh not in " +
         " (select distinct fwbh from " + table_fwzt + " where final_fwzt IS NOT NULL AND final_create_on = '" + endTime + " 23:59:59') " + strWhere + "limit " + pageSize + " offset(" + (pageIndex - 1) + ") * " + pageSize;

            }
            else if (category == "wjc")
            {

                sql = @"select  ROW_NUMBER() OVER ()index,*  from (select t1.xzq,t1.xzqdm,t1.town,t1.towndm,t1.villagedm,t1.village,t1.hzxm,t1.address,t1.fwbh  " +
                 " from zm_buildings t1 where  fwbh not in(select fwbh from " + table_fwzt + " where final_fwzt IS NOT NULL AND final_create_on = '" + endTime + " 23:59:59') " +
                "  and fwbh not in(select distinct t1.fwbh from zm_pc_baselog_cj t1,zm_pc_yhlog_cj t2 where t1.id=t2.base_id  and t1.create_on BETWEEN '"+startTime+ "' and '" + endTime + " 23:59:59' "+cj_strWhere+" and t1.fwbh not in  (select distinct fwbh from "+table_fwzt+ " where final_fwzt IS NOT NULL AND final_create_on ='"+endTime+" 23:59:59')) " +
                "   order by village ) t where  1=1  " + strWhere + " limit " + pageSize + " offset(" + (pageIndex - 1) + ") * " + pageSize;
            }
            else if (category == "yhtotal")
            {

                sql = @$"  select ROW_NUMBER() OVER ()index, * from ("+
     "    select xzq,xzqdm,towndm town,town towndm, villagedm village,village villagedm,hzxm,address,fwbh,check_id,check_name,create_on,'村级核验'  yhly,create_name,zgvalue,zg_time from " +
     "  (select distinct *  from (select t1.id, t1.xzqdm,t1.xzq,t1.town towndm,t1.towndm town,t1.village villagedm,t1.villagedm village,t2.hzxm,t2.address,t1.fwbh,t1.zg_value," +
     "  t1.create_on,t1.check_name, t1.jcly,t1.check_id,t1.check_nickname create_name,case when zg_value=0 then  '未整改' else '已整改' end zgvalue,zg_time from zm_pc_yhlog_dx_cj t1 left join zm_pc_yhlog_dxzgson_cj t3 on  t1.\"id\"=t3.yhlog_dx_id " +
     " left join zm_buildings t2 on t1.fwbh=t2.fwbh) zm_buildings where create_on  BETWEEN '"+startTime+"' and '"+endTime+" 23:59:59') t2  where fwbh not in(select distinct fwbh from " + table_fwzt + " where final_fwzt is not null and " +
     "  final_create_on='" + endTime + " 23:59:59')   " + strWhere +
                "   ) t where 1=1      "
+ "  " + "  limit " + pageSize + " offset(" + (pageIndex - 1) + ") * " + pageSize;
            }
            else if (category == "zgyh")
            {
                sql = @$"  select ROW_NUMBER() OVER ()index, * from (" +
     "    select xzq,xzqdm,towndm town,town towndm, villagedm village,village villagedm,hzxm,address,fwbh,check_id,check_name,create_on,'村级核验'  yhly,create_name,zgvalue,zg_time from " +
     "  (select distinct *  from (select t1.id, t1.xzqdm,t1.xzq,t1.town towndm,t1.towndm town,t1.village villagedm,t1.villagedm village,t2.hzxm,t2.address,t1.fwbh,t1.zg_value," +
     "  t1.create_on,t1.check_name, t1.jcly,t1.check_id,t1.check_nickname create_name,case when zg_value=0 then  '未整改' else '已整改' end zgvalue,zg_time from zm_pc_yhlog_dx_cj t1 left join zm_pc_yhlog_dxzgson_cj t3 on  t1.\"id\"=t3.yhlog_dx_id " +
     " left join zm_buildings t2 on t1.fwbh=t2.fwbh) zm_buildings where zg_time  BETWEEN '" + startTime + "' and '" + endTime + " 23:59:59' and  create_on  BETWEEN '" + startTime + "' and '" + endTime + " 23:59:59' ) t2  where fwbh not in(select distinct fwbh from " + table_fwzt + " where final_fwzt is not null and " +
     "  final_create_on='" + endTime + " 23:59:59')   " + strWhere +
                "   ) t where 1=1  and zgvalue='已整改'    "
+ "  " + "  limit " + pageSize + " offset(" + (pageIndex - 1) + ") * " + pageSize;
            }
            else if (category == "syyh")
            {
                sql = @$"  select ROW_NUMBER() OVER ()index, * from (" +
      "    select xzq,xzqdm,towndm town,town towndm, villagedm village,village villagedm,hzxm,address,fwbh,check_id,check_name,create_on,'村级核验'  yhly,create_name,zgvalue,zg_time from " +
      "  (select distinct *  from (select t1.id, t1.xzqdm,t1.xzq,t1.town towndm,t1.towndm town,t1.village villagedm,t1.villagedm village,t2.hzxm,t2.address,t1.fwbh,t1.zg_value," +
      "  t1.create_on,t1.check_name, t1.jcly,t1.check_id,t1.check_nickname create_name,case when zg_value=0 then  '未整改' else '已整改' end zgvalue,zg_time from zm_pc_yhlog_dx_cj t1 left join zm_pc_yhlog_dxzgson_cj t3 on  t1.\"id\"=t3.yhlog_dx_id " +
      " left join zm_buildings t2 on t1.fwbh=t2.fwbh) zm_buildings where create_on  BETWEEN '" + startTime + "' and '" + endTime + " 23:59:59') t2  where fwbh not in(select distinct fwbh from " + table_fwzt + " where final_fwzt is not null and " +
      "  final_create_on='" + endTime + " 23:59:59')   " + strWhere +
                 "   ) t where 1=1  and zgvalue='未整改'    "
 + "  " + "  limit " + pageSize + " offset(" + (pageIndex - 1) + ") * " + pageSize;
            }

            if (sql.Contains("insert into", StringComparison.OrdinalIgnoreCase)
          || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
          || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }


            string sql_count = " ";

            if (category == "yjc")
            {
                sql_count = @"select count(1) cnt from (select ROW_NUMBER() OVER(PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,t1.xzq,t1.xzqdm,
t1.town towndm,t1.towndm town,t1.villagedm village,t1.village villagedm,t1.hzxm,t1.address,t1.fwbh,t1.create_on from zm_pc_baselog_cj t1,zm_pc_yhlog_cj t2 
where t1.id=t2.base_id and t1.create_on BETWEEN '" + startTime + "' and '" + endTime + " 23:59:59') t1 " +
       "    where  create_on BETWEEN '" + startTime + "' and '" + endTime + " 23:59:59' and row_num=1  and fwbh not in " +
       " (select fwbh from " + table_fwzt + " where final_fwzt IS NOT NULL AND final_create_on = '" + endTime + " 23:59:59') " + strWhere;

            }
            else if (category == "wjc")
            {

                sql_count = @"select count(1) cnt from (select t1.xzq,t1.xzqdm,t1.town,t1.towndm,t1.villagedm,t1.village,t1.hzxm,t1.address,t1.fwbh  " +
                 " from zm_buildings t1 where  fwbh not in(select fwbh from " + table_fwzt + " where final_fwzt IS NOT NULL AND final_create_on = '" + endTime + " 23:59:59') " +
                "  and fwbh not in(select distinct t1.fwbh from zm_pc_baselog_cj t1,zm_pc_yhlog_cj t2 where t1.id=t2.base_id  and t1.create_on BETWEEN '" + startTime + "' and '" + endTime + " 23:59:59' " + cj_strWhere + " and t1.fwbh not in  (select distinct fwbh from " + table_fwzt + " where final_fwzt IS NOT NULL AND final_create_on ='" + endTime + " 23:59:59')) " +
                "    order by village ) t where  1=1  " + strWhere + " limit " + pageSize + " offset(" + (pageIndex - 1) + ") * " + pageSize;
            }
            else if (category == "yhtotal")
            {

                sql_count = @$"  select count(1) cnt from (" +
     "    select xzq,xzqdm,towndm town,town towndm, villagedm village,village villagedm,hzxm,address,fwbh,check_id,check_name,create_on,'村级核验'  yhly,create_name,zgvalue,zg_time from " +
     "  (select distinct *  from (select t1.id, t1.xzqdm,t1.xzq,t1.town towndm,t1.towndm town,t1.village villagedm,t1.villagedm village,t2.hzxm,t2.address,t1.fwbh,t1.zg_value," +
     "  t1.create_on,t1.check_name, t1.jcly,t1.check_id,t1.check_nickname create_name,case when zg_value=0 then  '未整改' else '已整改' end zgvalue,zg_time from zm_pc_yhlog_dx_cj t1 left join zm_pc_yhlog_dxzgson_cj t3 on  t1.\"id\"=t3.yhlog_dx_id " +
     " left join zm_buildings t2 on t1.fwbh=t2.fwbh) zm_buildings where create_on  BETWEEN '" + startTime + "' and '" + endTime + " 23:59:59') t2  where fwbh not in(select distinct fwbh from " + table_fwzt + " where final_fwzt is not null and " +
     "  final_create_on='" + endTime + " 23:59:59')   " + strWhere +
                "   ) t where 1=1      ";
            }
            else if (category == "zgyh")
            {
                sql_count = @$"  select count(1) cnt from (" +
     "    select xzq,xzqdm,towndm town,town towndm, villagedm village,village villagedm,hzxm,address,fwbh,check_id,check_name,create_on,'村级核验'  yhly,create_name,zgvalue,zg_time from " +
     "  (select distinct *  from (select t1.id, t1.xzqdm,t1.xzq,t1.town towndm,t1.towndm town,t1.village villagedm,t1.villagedm village,t2.hzxm,t2.address,t1.fwbh,t1.zg_value," +
     "  t1.create_on,t1.check_name, t1.jcly,t1.check_id,t1.check_nickname create_name,case when zg_value=0 then  '未整改' else '已整改' end zgvalue,zg_time from zm_pc_yhlog_dx_cj t1 left join zm_pc_yhlog_dxzgson_cj t3 on  t1.\"id\"=t3.yhlog_dx_id " +
     " left join zm_buildings t2 on t1.fwbh=t2.fwbh) zm_buildings where zg_time  BETWEEN '" + startTime + "' and '" + endTime + " 23:59:59' and  create_on  BETWEEN '" + startTime + "' and '" + endTime + " 23:59:59') t2  where fwbh not in(select distinct fwbh from " + table_fwzt + " where final_fwzt is not null and " +
     "  final_create_on='" + endTime + " 23:59:59')   " + strWhere +
                "   ) t where 1=1  and zgvalue='已整改'    ";
            }
            else if (category == "syyh")
            {

                sql_count = @$"  select count(1) cnt from (" +
     "    select xzq,xzqdm,towndm town,town towndm, villagedm village,village villagedm,hzxm,address,fwbh,check_id,check_name,create_on,'村级核验'  yhly,create_name,zgvalue,zg_time from " +
     "  (select distinct *  from (select t1.id, t1.xzqdm,t1.xzq,t1.town towndm,t1.towndm town,t1.village villagedm,t1.villagedm village,t2.hzxm,t2.address,t1.fwbh,t1.zg_value," +
     "  t1.create_on,t1.check_name, t1.jcly,t1.check_id,t1.check_nickname create_name,case when zg_value=0 then  '未整改' else '已整改' end zgvalue,zg_time from zm_pc_yhlog_dx_cj t1 left join zm_pc_yhlog_dxzgson_cj t3 on  t1.\"id\"=t3.yhlog_dx_id " +
     " left join zm_buildings t2 on t1.fwbh=t2.fwbh) zm_buildings where create_on  BETWEEN '" + startTime + "' and '" + endTime + " 23:59:59') t2  where fwbh not in(select distinct fwbh from " + table_fwzt + " where final_fwzt is not null and " +
     "  final_create_on='" + endTime + " 23:59:59')   " + strWhere +
                "   ) t where 1=1  and zgvalue='未整改'    ";
            }



            if (sql_count.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            //执行语句
            var list = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            int count = _dbContext.Database.GetDbConnection().QueryFirstOrDefault<int>(sql_count);

            return new RespRet<Page<object>>(true, "ok", new Page<object> { Items = list, NextPageIndex = pageIndex, PageTotal = count, Total = count });

        }

        /// <summary>
        /// 村级工作进展自查明细
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("village/evolvestatic/villagePostStaticDetail")]
        public RespRet<Page<object>> villagePostStaticDetail([FromQuery] string xzqdm, [FromQuery] string towndm, [FromQuery] string villagedm, [FromQuery] string category, [FromQuery] int pageIndex, [FromQuery] int limit, [FromQuery] string endTime, [FromQuery] string startTime, [FromQuery] string txtKeyWord)
        {
            if (pageIndex < 0)
            {
                return new RespRet<Page<object>>(false, "PageIndex must >= 0");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }

            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<Page<object>>(false, "用户名不存在");
            }

            var sbSql = new StringBuilder();
            var sbSqlWhere = new StringBuilder();
            var sqlParams = new List<NpgsqlParameter>();

            var sbSqlBuildingEx = new StringBuilder();

            string district = user.District;

            var options = new JsonSerializerOptions();
            options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
            Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);


            string where = "";
            if (!string.IsNullOrEmpty(txtKeyWord))
            {
                where += " and (hzxm like '%" + txtKeyWord + "%' or fwbh like '%" + txtKeyWord + "%' or address like '%" + txtKeyWord + "%')";
            }

            string strWhere = "  ";
            if (!string.IsNullOrEmpty(xzqdm) && !string.IsNullOrEmpty(towndm) && !string.IsNullOrEmpty(villagedm))
            {
                strWhere += " and xzqdm='" + xzqdm + "' and towndm='" + towndm + "' and villagedm='" + villagedm + "'";
            }



            string sql = " ";

            if (category == "1")
            {
                strWhere += " and date(create_on) between '" + startTime + "' and '" + endTime + "' " + where;
                sql = @" select ROW_NUMBER() OVER ()index,* from (select t1.xzqdm,t1.xzq,t1.town,t1.towndm,t1.village,t1.villagedm,t2.hzxm,t2.address,t1.fwbh,t1.create_on from zm_pc_yh_cj t1,(select * from  (select t1.* ,t2.fwzt from zm_buildings t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh ) zm_buildings  where fwzt is   null) t2  where 1 = 1 and t1.fwbh=t2.fwbh) t where 1=1  " + strWhere + "limit " + limit + " offset(" + pageIndex + ") * " + limit;
            }
            else if (category == "2")
            {

                sql = @"select ROW_NUMBER() OVER ()index,* from (select xzq,xzqdm,town,towndm,villagedm,village,hzxm,address,fwbh from   zm_buildings where  fwbh not in(select fwbh from zm_pc_base_cj where fwzt is not null) and fwbh not in(select fwbh from  zm_pc_base_sqjz where yhly='4' and fwzt is not null)    and fwbh not in(" +
                    "     SELECT fwbh   FROM(select t1.* from  (select ROW_NUMBER() OVER(PARTITION BY m1.fwbh ORDER BY m1.create_on DESC) AS row_num, " +
                    "  m1.* from zm_pc_baselog_cj m1 ,zm_pc_yhlog_cj m2 where m1.id=m2.base_id and  date(m1.create_on)" +
                    "between '" + startTime + "' and '" + endTime + "' ) t1  WHERE  row_num=1  and date(create_on)" +
                   " between '" + startTime + "' and '" + endTime + "'  and fwbh not in(select fwbh from zm_pc_base_cj where fwzt is not null) and fwbh not in(select fwbh from  zm_pc_base_sqjz where yhly='4' and fwzt is not null)" +
                    "	and village='" + villagedm + "'  )  t  order by village)    " + strWhere + where + "  order by village) t  where 1=1  limit " + limit + " offset(" + pageIndex + ") * " + limit;
            }
            else if (category == "3")
            {
                strWhere += " and date(create_on) between '" + startTime + "' and '" + endTime + "' " + where;
                sql = @"select ROW_NUMBER() OVER ()index,* from (select distinct * from (select t1.xzqdm,t1.xzq,t1.town,t1.towndm,t1.village,t1.villagedm,t2.hzxm,t2.address,t1.fwbh,t1.zg_value,t1.create_on from zm_pc_yhlog_dx_cj t1,(select * from  (select t1.* ,t2.fwzt from zm_buildings t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh ) zm_buildings  where fwzt is   null) t2  where 1 = 1 and  t1.fwbh=t2.fwbh) t) t where  1=1 " + strWhere + "limit " + limit + " offset(" + pageIndex + ") * " + limit;
            }
            else if (category == "4")
            {
                strWhere += " and date(create_on) between '" + startTime + "' and '" + endTime + "' " + where;
                sql = @"select ROW_NUMBER() OVER ()index,* from (select distinct * from (select t1.xzqdm,t1.xzq,t1.town,t1.towndm,t1.village,t1.villagedm,t2.hzxm,t2.address,t1.fwbh,t1.zg_value,t1.create_on from zm_pc_yhlog_dx_cj t1,(select * from  (select t1.* ,t2.fwzt from zm_buildings t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh ) zm_buildings  where fwzt is   null) t2  where 1 = 1 and  t1.fwbh=t2.fwbh) t where zg_value='1') t where 1=1  " + strWhere + "limit " + limit + " offset(" + pageIndex + ") * " + limit;
            }
            else if (category == "5")
            {
                strWhere += " and date(create_on) between '" + startTime + "' and '" + endTime + "' " + where;
                sql = @"select ROW_NUMBER() OVER ()index,* from (select distinct * from (select t1.xzqdm,t1.xzq,t1.town,t1.towndm,t1.village,t1.villagedm,t2.hzxm,t2.address,t1.fwbh,t1.zg_value,t1.create_on from zm_pc_yhlog_dx_cj t1,(select * from  (select t1.* ,t2.fwzt from zm_buildings t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh ) zm_buildings  where fwzt is   null) t2  where 1 = 1 and t1.fwbh=t2.fwbh and zg_value='0' ) t) t where 1=1  " + strWhere + "limit " + limit + " offset(" + pageIndex + ") * " + limit;
            }


            if (sql.Contains("insert into", StringComparison.OrdinalIgnoreCase)
          || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
          || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }


            string sql_count = " ";

            if (category == "1")
            {
                sql_count = @" select count(1) cnt  from (select t1.xzqdm,t1.xzq,t1.town,t1.towndm,t1.village,t1.villagedm,t2.hzxm,t2.address,t1.fwbh,t1.create_on from zm_pc_yh_cj t1,(select * from  (select t1.* ,t2.fwzt from zm_buildings t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh ) zm_buildings  where fwzt is   null) t2  where 1 = 1 and t1.fwbh=t2.fwbh) t where 1=1  " + strWhere + " ";
            }
            else if (category == "2")
            {
                sql_count = @"select count(1) cnt from (	 select * from (select t1.xzq,t1.xzqdm,t1.town,t1.towndm,t1.villagedm,t1.village,t1.hzxm,t1.address,t1.fwbh, 
                      t2.fwzt jcfwzt,t3.fwzt hcfwzt from zm_buildings t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh  left join  (select fwbh,fwzt from  zm_pc_base_sqjz where yhly='4') t3 on t1.fwbh=t3.fwbh) m1  
                      WHERE  (jcfwzt is null and hcfwzt is null )  and  villagedm='" + villagedm + "'  and fwbh not in(" +
                  "     SELECT fwbh   FROM(select t1.* from  (select ROW_NUMBER() OVER(PARTITION BY m1.fwbh ORDER BY m1.create_on DESC) AS row_num, " +
                  "  m1.* from zm_pc_baselog_cj m1 ,zm_pc_yhlog_cj m2 where m1.id=m2.base_id and  date(m1.create_on)" +
                  "between '" + startTime + "' and '" + endTime + "' ) t1  WHERE  row_num=1  and date(create_on)" +
                 " between '" + startTime + "' and '" + endTime + "'  and fwbh not in(select fwbh from zm_pc_base_cj where fwzt is not null) and fwbh not in(select fwbh from  zm_pc_base_sqjz where yhly='4' and fwzt is not null)" +
                  "	and village='" + villagedm + "'  )  t  order by village)     " + strWhere + where + "  order by village) t  where 1=1";
            }
            else if (category == "3")
            {
                sql_count = @"select count(1) cnt  from (select distinct * from (select t1.xzqdm,t1.xzq,t1.town,t1.towndm,t1.village,t1.villagedm,t2.hzxm,t2.address,t1.fwbh,t1.zg_value,t1.create_on from zm_pc_yhlog_dx_cj t1,(select * from  (select t1.* ,t2.fwzt from zm_buildings t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh ) zm_buildings  where fwzt is   null) t2  where 1 = 1 and  t1.fwbh=t2.fwbh) t) t where 1=1  " + strWhere + " ";
            }
            else if (category == "4")
            {
                sql_count = @"select count(1) cnt  from (select distinct * from (select t1.xzqdm,t1.xzq,t1.town,t1.towndm,t1.village,t1.villagedm,t2.hzxm,t2.address,t1.fwbh,t1.zg_value,t1.create_on from zm_pc_yhlog_dx_cj t1,(select * from  (select t1.* ,t2.fwzt from zm_buildings t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh ) zm_buildings  where fwzt is   null) t2  where 1 = 1 and  t1.fwbh=t2.fwbh and zg_value='1') t where zg_value='1') t where 1=1  " + strWhere + "";
            }
            else if (category == "5")
            {
                sql_count = @"select count(1) cnt  from (select distinct * from (select t1.xzqdm,t1.xzq,t1.town,t1.towndm,t1.village,t1.villagedm,t2.hzxm,t2.address,t1.fwbh,t1.zg_value,t1.create_on from zm_pc_yhlog_dx_cj t1,zm_b(select * from  (select t1.* ,t2.fwzt from zm_buildings t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh ) zm_buildings  where fwzt is   null)uildings t2  where 1 = 1 and  t1.fwbh=t2.fwbh and zg_value='0' ) t) t where 1=1  " + strWhere + "";
            }


            if (sql_count.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            //执行语句
            var list = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            int count = _dbContext.Database.GetDbConnection().QueryFirstOrDefault<int>(sql_count);

            return new RespRet<Page<object>>(true, "ok", new Page<object> { Items = list, NextPageIndex = pageIndex, PageTotal = count, Total = count });

        }

        /// <summary>
        /// 获取当前房屋的检查记录中的详细信息
        /// </summary>
        /// <param name="id">记录ID</param>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("village/{id}/restinfo/")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<HourseBaseInfo>), 200)]
        public RespRet<HourseBaseInfo> restinfo([FromRoute] string id)
        {
            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<HourseBaseInfo>(false, "user not logined");
            }

            HourseBaseInfo model = new HourseBaseInfo();
            string sql = "  select *,case when (coalesce(yhnum,0) >0) then  CAST('21' AS INTEGER) else  CAST('20' AS INTEGER)  end AS pczt from " +
                "( select b1.*,yhnum from (select t1.id,t1.fwbh,t1.yhly,t1.xzqdm,t1.town,t1.village,address,yhfwbh,hzxm,jzrks,zzrs,cs,fjsl,jzfjs,jzscxyaqcksl," +
                "jzxyssltsl,jzscmj,ckfhwggptzlsl,bdfwjzrydddzxcsl,mhqsl,fytsmz,xggdjsbp,jzsyjyplmhxts,jzszlwxdlgybjqs,wdwhjgfjgqsyyrkrgbdj,sstdsyyrkrclzx," +
                "gryssdcsszblyyqksddp,case when cjzwqtjyyt =0 then '否' else cjzwqtjyytmsg end cjzwqtjyytmsg,case when cs=1 and jzscxyaqcksl>=2 then '已达标'  else case when cs>=2 and jzscxyaqcksl>=2  and jzxyssltsl >=2 then '已达标' else sstdjsqk end  end jsrysstd,sstdjsqk,to_char(t1.create_on::timestamp(0) without time zone, 'YYYY-MM-DD HH24:MI:SS') pcsj,cjzwqtjyyt,use_electric_bicycle,use_bottled_liquefied_gas,use_stacked_items," +
                "mhqwbcwhyx,jyplmhxtwbcwhyx,ygbjwfwhyx,use_electric_bicycle_photo,use_bottled_liquefied_gas_photo,use_stacked_items_photo,mhqwbcwhyx_photo," +
                "jyplmhxtwbcwhyx_photo,ygbjwfwhyx_photo,use_electric_bicycle_msg,use_bottled_liquefied_gas_msg,use_stacked_items_msg,mhqwbcwhyx_msg," +
                "jyplmhxtwbcwhyx_msg,ygbjwfwhyx_msg,use_electric_bicycle_zgqk,use_bottled_liquefied_gas_zgqk,use_stacked_items_zgqk,mhqwbcwhyx_zgqk," +
                "jyplmhxtwbcwhyx_zgqk,ygbjwfwhyx_msg_zgqk, use_electric_bicycle_zgphoto,use_bottled_liquefied_gas_zgphoto,use_stacked_items_zgphoto," +
                "mhqwbcwhyx_zgphoto,jyplmhxtwbcwhyx_zgphoto,ygbjwfwhyx_msg_zgphoto,slljdx,slljdx_photo, slljdx_msg,slljdx_zgqk,slljdx_zgphoto,zxqlddcsl," +
                "zxqlyhsyqsl,sign,notes,t1.create_name,t1.create_on from zm_pc_baselog_cj t1, zm_pc_yhlog_cj t2 where t1.id = t2.base_id ) b1 left join (select fwbh,base_id,COALESCE(yhnum,0) yhnum, " +
                "COALESCE(zgnum,0) zgnum from  zm_pc_yhlog_cj t1 inner join  (select count(fwbh) yhnum,yhlog_id,sum(case when zg_value='1' then 1 else 0 end) zgnum " +
                "from zm_pc_yhlog_dx_cj  group by yhlog_id) t2 on t1.id=t2.yhlog_id) b2  on b1.id=b2.base_id) t  where id ='" + id + "'";

            var item = _dbContext.Database.GetDbConnection().Query<BuildingPcYhLogDetailDto>(sql).ToList();

            model.BuildingPcYhLogDetailDtoList = item;
            ///整改
            string sql_rectification = @"  select *from (select m1.*,check_id,check_name,zg_zp,zg_time,zg_by zg_name   from 
                    (select t1.base_id,t1.fwbh,t2.id dx_id from zm_pc_yhlog_cj t1 left join zm_pc_yhlog_dx_cj t2 on t1.id=t2.yhlog_id ) m1 ,
                    zm_pc_yhlog_dxzgson_cj m2  where m1.dx_id=m2.yhlog_dx_id ) t where base_id='" + id + "'";

            var itemLIst = _dbContext.Database.GetDbConnection().Query<RectificationYh>(sql_rectification).ToList();

            model.RectificationYhList = itemLIst;


            return new RespRet<HourseBaseInfo>(true, "Ok", model);
        }

        /// <summary>
        /// 导出检查数据
        /// </summary>
        /// <param name="pageRequest">type check</param>
        /// <returns></returns>
        [HttpGet("village/evolvestatic/ExportExcel")]
        [Produces("application/octet-stream")]
        //[ProducesResponseType(200)]
        //[ProducesResponseType(401)]
        public IActionResult ExportExcel([FromQuery] string startTime, [FromQuery] string endTime)
        {
            try
            {
                //var list = new List<VillageFireSafetyAttrDto>();
                var userNumber = this.CurrentUser();

                var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
                var sbSql = new StringBuilder();
                var sbSqlWhere = new StringBuilder();
                var sqlParams = new List<NpgsqlParameter>();

                var sbSqlBuildingEx = new StringBuilder();

                string district = user.District;

                var options = new JsonSerializerOptions();
                options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
                Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);


                string strWhere = "  ";

                if (user.RoleType == 1 || user.RoleType == 2)
                {
                    strWhere += " and xzq!='东城区' and xzq!='西城区' ";
                }
                else if (user.RoleType == 3)  //区级
                {
                    strWhere += " and xzqdm='" + model.RegionCode + "'";
                }
                else if (user.RoleType == 4)
                {
                    string tdm = model.RegionCode;
                    if (model.RegionCode.Length != 12)
                    {
                        tdm = model.RegionCode + "000";
                    }
                    strWhere += " and towndm='" + tdm + "'";
                }
                else if (user.RoleType == 5)
                {
                    strWhere += " and villagedm='" + model.RegionCode + "'";
                }

                if (string.IsNullOrEmpty(startTime) && string.IsNullOrEmpty(endTime))
                {
                    startTime = DateTime.Now.ToShortDateString();
                    endTime = DateTime.Now.ToShortDateString();
                }
                string toDay = DateTime.Now.ToString("yyyy-MM-dd");
                string table_fwzt = "";
                if (!string.IsNullOrEmpty(endTime))
                {
                    DateTime endData = Convert.ToDateTime(endTime);
                    table_fwzt = "zm_building_fwzt_" + endData.Year;
                }
                else
                {
                    DateTime endData = Convert.ToDateTime(toDay);
                    table_fwzt = "zm_building_fwzt_" + endData.Year;
                }

                strWhere += "  order by xzqdm,towndm asc ,villagedm asc";
                string sql = @"select ROW_NUMBER() OVER ()index,* from jcqkcjtext('" + startTime + "','" + endTime + " 23:59:59','','" + table_fwzt + "')  where 1 = 1 and xzq!= '东城区' and xzq!= '西城区'  " + strWhere;

                //写入缓存
                var data = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
                IWorkbook wb = new XSSFWorkbook();
                ISheet sheet = wb.CreateSheet(model.RegionName + "");
                // 第一行 标题
                IRow row = sheet.CreateRow(0);
                row.CreateCell(0).SetCellValue("序号");
                row.CreateCell(1).SetCellValue("区名称");
                row.CreateCell(2).SetCellValue("乡镇/街名称");
                row.CreateCell(3).SetCellValue("村/社区名称");
                row.CreateCell(4).SetCellValue("房屋总数(栋)");
                row.CreateCell(5).SetCellValue("自查总数(栋)");
                row.CreateCell(6).SetCellValue("自查率");
                row.CreateCell(7).SetCellValue("隐患数量（个）");
                row.CreateCell(8).SetCellValue("整改数量（个）");
                row.CreateCell(9).SetCellValue("剩余隐患数量（个）");

                // 第二行 写数据
                int i = 1;
                foreach (object obj in data)
                {
                    Dictionary<string, object> dictionary = new Dictionary<string, object>();
                    if (obj != null)
                    {

                        var properties = obj as IDictionary<string, object>;
                        row = sheet.CreateRow(i);
                        foreach (var kvp in properties)
                        {
                            var val = kvp.Value + "";
                            if (kvp.Key == "index")
                            {
                                row.CreateCell(0).SetCellValue(i);
                            }
                            else if (kvp.Key == "village")
                            {
                                row.CreateCell(3).SetCellValue(val);
                            }
                            else if (kvp.Key == "town")
                            {
                                row.CreateCell(2).SetCellValue(val);
                            }
                            else if (kvp.Key == "xzq")
                            {
                                row.CreateCell(1).SetCellValue(val);
                            }
                            else if (kvp.Key == "zcount")
                            {
                                row.CreateCell(4).SetCellValue(val);
                            }
                            else if (kvp.Key == "jccount")
                            {
                                row.CreateCell(5).SetCellValue(val);
                            }
                            else if (kvp.Key == "yhcount")
                            {
                                row.CreateCell(7).SetCellValue(val);
                            }
                            else if (kvp.Key == "zgcount")
                            {
                                row.CreateCell(8).SetCellValue(val);
                            }
                            else if (kvp.Key == "jcl")
                            {
                                row.CreateCell(6).SetCellValue(val);
                            }
                            else if (kvp.Key == "syyhcount")
                            {
                                row.CreateCell(9).SetCellValue(val);
                            }
                        }

                        i++;
                    }
                }

                // 写 WorkBook信息到 内存流中
                byte[] buffer = null;
                using (MemoryStream ms = new MemoryStream())
                {
                    wb.Write(ms);
                    buffer = ms.ToArray();
                }
                return File(buffer, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "村民经营性自建房消防安全排查治理村級工作进展统计.xlsx");
            }
            catch (Exception ex)
            {
                throw;
            }

        }


        /// <summary>
        /// 导出自查房屋明细数据
        /// </summary>
        /// <param name="pageRequest">type check</param>
        /// <returns></returns>
        [HttpGet("village/evolvestatic/ExportDetailedExcel")]
        [Produces("application/octet-stream")]
        //[ProducesResponseType(200)]
        //[ProducesResponseType(401)]
        public IActionResult ExportDetailedExcel([FromQuery] string xzqdm, [FromQuery] string towndm, [FromQuery] string villagedm, [FromQuery] string startTime, [FromQuery] string endTime)
        {
            try
            {
                //var list = new List<VillageFireSafetyAttrDto>();
                var userNumber = this.CurrentUser();

                var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
                var sbSql = new StringBuilder();
                var sbSqlWhere = new StringBuilder();
                var sqlParams = new List<NpgsqlParameter>();

                var sbSqlBuildingEx = new StringBuilder();

                string district = user.District;

                var options = new JsonSerializerOptions();
                options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
                Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);


                string strWhere = "  ";

                if (user.RoleType == 1 || user.RoleType == 2)
                {
                    strWhere += " and xzq!='东城区' and xzq!='西城区' ";

                }
                else if (user.RoleType == 3)  //区级
                {
                    strWhere += " and xzqdm='" + model.RegionCode + "'";
                }
                else if (user.RoleType == 4)
                {
                    string tdm = model.RegionCode;
                    if (model.RegionCode.Length != 12)
                    {
                        tdm = model.RegionCode + "000";
                    }
                    strWhere += " and towndm='" + tdm + "'";
                }
                else if (user.RoleType == 5)
                {
                    strWhere += " and villagedm='" + model.RegionCode + "'";
                }

                if (!string.IsNullOrEmpty(xzqdm))
                {
                    strWhere += " and xzqdm='" + xzqdm + "'";
                }
                if (!string.IsNullOrEmpty(towndm))
                {
                    strWhere += " and towndm='" + towndm + "'";
                }
                if (!string.IsNullOrEmpty(villagedm))
                {
                    strWhere += " and villagedm='" + villagedm + "'";
                }

                string today = DateTime.Now.ToString("yyyy-MM-dd");
                string table_fwzt = "";
                if (!string.IsNullOrEmpty(endTime))
                {
                    DateTime endData = Convert.ToDateTime(endTime);
                    table_fwzt = "zm_building_fwzt_" + endData.Year;
                }
                else
                {
                    DateTime endData = Convert.ToDateTime(today);
                    table_fwzt = "zm_building_fwzt_" + endData.Year;
                }
                //string sql = @" select ROW_NUMBER() OVER ()index ,* from (select t1.xzqdm,t1.xzq,t1.town,t1.towndm,t1.village,t1.villagedm,t1.hzxm,t1.address,t1.fwbh,
                //case when  t2.create_on is null then '未检查' else '已检查' end jczt from ( select *from(select t1.* ,t2.fwzt from zm_buildings t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh ) zm_buildings  where fwzt is   null) t1 left join (select xzqdm,towndm,villagedm,fwbh,
                //create_on from zm_pc_yh_cj where date(create_on) BETWEEN '" + startTime + "' and  '" + endTime + "') t2 on t1.fwbh=t2.fwbh) t where 1=1  " + strWhere + "  order by xzqdm,towndm asc,villagedm asc";
                string sql = @"  select ROW_NUMBER() OVER ()index ,*  FROM (  SELECT t1.xzq,t1.xzqdm,t1.towndm,t1.town,t1.village,t1.villagedm,t1.hzxm,t1.address,t1.fwbh,case when  t2.create_on is null then '未检查' else '已检查' end jczt
                   FROM  (select xzqdm, xzq ,towndm,town,villagedm,village,hzxm,address,fwbh from zm_buildings where fwbh not in
(select distinct fwbh from " + table_fwzt + " where final_fwzt is not null and final_create_on='"+endTime+" 23:59:59')) t1"+
"              LEFT JOIN ( select * from (select ROW_NUMBER() OVER (PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num, t1.xzq,t1.xzqdm,t1.towndm,"+
" t1.town,t1.villagedm,t1.village,t1.hzxm,t1.address,t1.fwbh,t1.create_on from zm_pc_baselog_cj t1,  zm_pc_yhlog_cj t2 where t1.id=t2.base_id  and t1.create_on"+ 
" BETWEEN '" +startTime+"' and '"+endTime+" 23:59:59') t   where row_num=1  and create_on BETWEEN '"+startTime+"' and '"+endTime+" 23:59:59'  and fwbh not "+
"in(select distinct fwbh from "+table_fwzt+" where final_fwzt is not null and final_create_on='"+ endTime + " 23:59:59') 	 ) t2 ON ( t1.fwbh=t2.fwbh)) "+
"t where  1=1 "+strWhere ;



                //写入缓存
                var data = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
                IWorkbook wb = new XSSFWorkbook();
                ISheet sheet = wb.CreateSheet(model.RegionName + "");
                // 第一行 标题
                IRow row = sheet.CreateRow(0);
                row.CreateCell(0).SetCellValue("序号");
                row.CreateCell(1).SetCellValue("区名称");
                row.CreateCell(2).SetCellValue("乡镇/街名称");
                row.CreateCell(3).SetCellValue("村/社区名称");
                row.CreateCell(4).SetCellValue("房主姓名");
                row.CreateCell(5).SetCellValue("地址");
                row.CreateCell(6).SetCellValue("房屋编号");
                row.CreateCell(7).SetCellValue("检查状态");
                // 第二行 写数据
                int i = 1;
                foreach (object obj in data)
                {
                    Dictionary<string, object> dictionary = new Dictionary<string, object>();
                    if (obj != null)
                    {

                        var properties = obj as IDictionary<string, object>;
                        row = sheet.CreateRow(i);
                        foreach (var kvp in properties)
                        {
                            var val = kvp.Value + "";
                            if (kvp.Key == "index")
                            {
                                row.CreateCell(0).SetCellValue(i);
                            }
                            else if (kvp.Key == "village")
                            {
                                row.CreateCell(3).SetCellValue(val);
                            }
                            else if (kvp.Key == "town")
                            {
                                row.CreateCell(2).SetCellValue(val);
                            }
                            else if (kvp.Key == "xzq")
                            {
                                row.CreateCell(1).SetCellValue(val);
                            }
                            else if (kvp.Key == "hzxm")
                            {
                                row.CreateCell(4).SetCellValue(val);
                            }
                            else if (kvp.Key == "address")
                            {
                                row.CreateCell(5).SetCellValue(val);
                            }
                            else if (kvp.Key == "fwbh")
                            {
                                row.CreateCell(6).SetCellValue(val);
                            }
                            else if (kvp.Key == "jczt")
                            {
                                row.CreateCell(7).SetCellValue(val);
                            }

                        }

                        i++;
                    }
                }

                // 写 WorkBook信息到 内存流中
                byte[] buffer = null;
                using (MemoryStream ms = new MemoryStream())
                {
                    wb.Write(ms);
                    buffer = ms.ToArray();
                }
                return File(buffer, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "村民经营性自建房消防安全排查治理村級工作进展统计（检查明细）.xlsx");
            }
            catch (Exception ex)
            {
                throw;
            }
        }

        /// <summary>
        /// 某栋房屋的检查、自查记录
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [AllowAnonymous]
        [HttpGet("village/baseInfo/baseInfoDetaile")]
        public RespRet<Page<object>> baseInfoDetaile([FromQuery] string fwbh, [FromQuery] int pageIndex, [FromQuery] int limit)
        {
            if (pageIndex < 0)
            {
                return new RespRet<Page<object>>(false, "PageIndex must >= 0");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }


            string strWhere = "";
            string sql = @"select  ROW_NUMBER() OVER ()index ,*   from ( select b1.*,COALESCE(yhnum,0) yhnum,case when yhnum is null then '已检查无隐患' else  '已检查有隐患' end yhzt,COALESCE(zgnum,0) zgnum, case when fwzt is null then '正常出租' else case when fwzt=1 then '不出租' else  '已拆除' end end fwztname,
                         case when zgnum is null then '/' else case when zgnum=0 then '未整改' else  '已整改' end end zgzt,'详情' cz  from (select t2.hzxm,t2.address,t1.fwbh,t1.jsrysstd,t1.yhlog_id, t1.id,to_char(t1.create_on::timestamp(0) without time zone, 'YYYY-MM-DD HH24:MI:SS') pcsj ,t1.create_name ,fwzt
                           from (select m1.*,m2.id yhlog_id from zm_pc_baselog_cj m1, zm_pc_yhlog_cj m2 where m1.id=m2.base_id) t1,zm_buildings t2 where t1.fwbh=t2.fwbh ) b1 left join 
                           (select count(fwbh) yhnum, yhlog_id, sum(case when zg_value= '1' then 1 else 0 end) zgnum from zm_pc_yhlog_dx_cj 
                           group by yhlog_id) b2 on b1.yhlog_id = b2.yhlog_id  order by pcsj desc)  t   
                       where fwbh='" + fwbh + "' " + strWhere + "   limit " + limit + " offset " + limit * pageIndex;


            if (sql.Contains("insert into", StringComparison.OrdinalIgnoreCase)
         || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
         || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            string sql_count = @"select  count(1) cnt  from ( select b1.*,COALESCE(yhnum,0) yhnum,case when yhnum is null then '已检查无隐患' else  '已检查有隐患' end yhzt,COALESCE(zgnum,0) zgnum, case when fwzt is null then '正常出租' else case when fwzt=1 then '不出租' else  '已拆除' end end fwztname,
                         case when zgnum is null then '/' else case when zgnum=0 then '未整改' else  '已整改' end end zgzt,'详情' cz  from (select t2.hzxm,t2.address,t1.fwbh,t1.jsrysstd,t1.yhlog_id,t1.id,to_char(t1.create_on::timestamp(0) without time zone, 'YYYY-MM-DD HH24:MI:SS') pcsj  ,t1.create_name ,fwzt
                           from (select m1.*,m2.id yhlog_id from zm_pc_baselog_cj m1, zm_pc_yhlog_cj m2 where m1.id=m2.base_id) t1,zm_buildings t2 where t1.fwbh=t2.fwbh ) b1 left join 
                           (select count(fwbh) yhnum, yhlog_id, sum(case when zg_value= '1' then 1 else 0 end) zgnum from zm_pc_yhlog_dx_cj 
                           group by yhlog_id) b2 on b1.yhlog_id = b2.yhlog_id order by pcsj desc)  t    where fwbh='" + fwbh + "'" + strWhere;

            if (sql_count.Contains("insert into", StringComparison.OrdinalIgnoreCase)
          || sql_count.Contains("delete from", StringComparison.OrdinalIgnoreCase)
          || sql_count.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            //执行语句
            var list = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            int count = _dbContext.Database.GetDbConnection().QueryFirstOrDefault<int>(sql_count);

            return new RespRet<Page<object>>(true, "ok", new Page<object> { Items = list, NextPageIndex = pageIndex, PageTotal = count, Total = count });

        }


        /// <summary>
        /// 分类导出明细
        /// </summary>
        /// <param name="pageRequest">type check</param>
        /// <returns></returns>
        [HttpGet("village/evolvestatic/ExportHouseDetailedExcel")]
        [Produces("application/octet-stream")]
        //[ProducesResponseType(200)]
        //[ProducesResponseType(401)]
        public IActionResult ExportHouseDetailedExcel([FromQuery] string xzqdm, [FromQuery] string towndm, [FromQuery] string villagedm, [FromQuery] string startTime, [FromQuery] string endTime, [FromQuery] string category)
        {
            try
            {
                //var list = new List<VillageFireSafetyAttrDto>();
                var userNumber = this.CurrentUser();

                var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
                var sbSql = new StringBuilder();
                var sbSqlWhere = new StringBuilder();
                var sqlParams = new List<NpgsqlParameter>();

                var sbSqlBuildingEx = new StringBuilder();

                string district = user.District;

                var options = new JsonSerializerOptions();
                options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
                Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);


                string strWhere = "  ";

                if (user.RoleType == 1 || user.RoleType == 2)
                {
                    strWhere += " and xzq!='东城区' and xzq!='西城区' ";
                }
                else if (user.RoleType == 3)  //区级
                {
                    strWhere += " and xzqdm='" + model.RegionCode + "'";
                }
                else if (user.RoleType == 4)
                {
                    string tdm = model.RegionCode;
                    if (model.RegionCode.Length != 12)
                    {
                        tdm = model.RegionCode + "000";
                    }
                    strWhere += " and towndm='" + tdm + "'";
                }
                else if (user.RoleType == 5)
                {
                    strWhere += " and villagedm='" + model.RegionCode + "'";
                }

                if (!string.IsNullOrEmpty(xzqdm))
                {
                    strWhere += " and xzqdm='" + xzqdm + "'";
                }
                if (!string.IsNullOrEmpty(towndm))
                {
                    strWhere += " and towndm='" + towndm + "'";
                }
                if (!string.IsNullOrEmpty(villagedm))
                {
                    strWhere += " and villagedm='" + villagedm + "'";
                }

                string sql = "";
                string filename = "";

                if (category == "1")
                {
                    filename = startTime + "-" + endTime + "已检查明细";
                    strWhere += " and date(create_on) between '" + startTime + "' and '" + endTime + "' ";
                    sql = @" select ROW_NUMBER() OVER ()index,* from (select t1.xzqdm,t1.xzq,t1.town,t1.towndm,t1.village,t1.villagedm,t2.hzxm,t2.address,t1.fwbh,t1.create_on from zm_pc_yh_cj t1,zm_buildings t2  where 1 = 1 and t1.fwbh=t2.fwbh) t where 1=1 " + strWhere;
                }
                else if (category == "2")
                {
                    filename = startTime + "-" + endTime + "未检查明细";
                    sql = @"select ROW_NUMBER() OVER ()index,* from (select xzq,xzqdm,town,towndm,villagedm,village,hzxm,address,fwbh from   zm_buildings where  fwbh not in(select fwbh from zm_pc_base_cj where fwzt is not null) and fwbh not in(select fwbh from  zm_pc_base_sqjz where yhly='4' and fwzt is not null)    and fwbh not in(" +
                        "     SELECT fwbh   FROM(select t1.* from  (select ROW_NUMBER() OVER(PARTITION BY m1.fwbh ORDER BY m1.create_on DESC) AS row_num, " +
                        "  m1.* from zm_pc_baselog_cj m1 ,zm_pc_yhlog_cj m2 where m1.id=m2.base_id and  date(m1.create_on)" +
                        "between '" + startTime + "' and '" + endTime + "' ) t1  WHERE  row_num=1  and date(create_on)" +
                       " between '" + startTime + "' and '" + endTime + "'  and fwbh not in(select fwbh from zm_pc_base_cj where fwzt is not null) and fwbh not in(select fwbh from  zm_pc_base_sqjz where yhly='4' and fwzt is not null)" +
                        "	and village='" + villagedm + "'  )  t  order by village)    " + strWhere + "  order by village) t  where 1=1 ";
                }
                else if (category == "3")
                {
                    filename = startTime + "-" + endTime + "发现隐患情况";
                    strWhere += " and date(create_on) between '" + startTime + "' and '" + endTime + "' ";
                    sql = @"select ROW_NUMBER() OVER ()index,* from (select distinct * from (select t1.xzqdm,t1.xzq,t1.town,t1.towndm,t1.village,t1.villagedm,t2.hzxm,t2.address,t1.fwbh,t1.zg_value,t1.create_on from zm_pc_yhlog_dx_cj t1,(select * from  (select t1.* ,t2.fwzt from zm_buildings t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh ) zm_buildings  where fwzt is   null) t2  where 1 = 1 and  t1.fwbh=t2.fwbh) t) t where 1=1  " + strWhere;
                }
                else if (category == "4")
                {
                    filename = startTime + "-" + endTime + "已整改隐患情况";
                    strWhere += " and date(create_on) between '" + startTime + "' and '" + endTime + "' ";
                    sql = @"select ROW_NUMBER() OVER ()index,* from (select distinct * from (select t1.xzqdm,t1.xzq,t1.town,t1.towndm,t1.village,t1.villagedm,t2.hzxm,t2.address,t1.fwbh,t1.zg_value,t1.create_on from zm_pc_yhlog_dx_cj t1,(select * from  (select t1.* ,t2.fwzt from zm_buildings t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh ) zm_buildings  where fwzt is   null) t2  where 1 = 1 and  t1.fwbh=t2.fwbh) t where zg_value='1') t where 1=1  " + strWhere;
                }
                else if (category == "5")
                {
                    filename = startTime + "-" + endTime + "未整改隐患情况";
                    strWhere += " and date(create_on) between '" + startTime + "' and '" + endTime + "' ";
                    sql = @"select ROW_NUMBER() OVER ()index,* from (select distinct * from (select t1.xzqdm,t1.xzq,t1.town,t1.towndm,t1.village,t1.villagedm,t2.hzxm,t2.address,t1.fwbh,t1.zg_value,t1.create_on from zm_pc_yhlog_dx_cj t1,(select * from  (select t1.* ,t2.fwzt from zm_buildings t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh ) zm_buildings  where fwzt is   null) t2  where 1 = 1 and t1.fwbh=t2.fwbh and zg_value='0' ) t) t where 1=1  " + strWhere;
                }

                //写入缓存
                var data = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
                IWorkbook wb = new XSSFWorkbook();
                ISheet sheet = wb.CreateSheet(model.RegionName + "");
                // 第一行 标题
                IRow row = sheet.CreateRow(0);
                row.CreateCell(0).SetCellValue("序号");
                row.CreateCell(1).SetCellValue("区名称");
                row.CreateCell(2).SetCellValue("乡镇/街名称");
                row.CreateCell(3).SetCellValue("村/社区名称");
                row.CreateCell(4).SetCellValue("房主姓名");
                row.CreateCell(5).SetCellValue("地址");
                row.CreateCell(6).SetCellValue("房屋编号");

                // 第二行 写数据
                int i = 1;
                foreach (object obj in data)
                {
                    Dictionary<string, object> dictionary = new Dictionary<string, object>();
                    if (obj != null)
                    {

                        var properties = obj as IDictionary<string, object>;
                        row = sheet.CreateRow(i);
                        foreach (var kvp in properties)
                        {
                            var val = kvp.Value + "";
                            if (kvp.Key == "index")
                            {
                                row.CreateCell(0).SetCellValue(i);
                            }
                            else if (kvp.Key == "village")
                            {
                                row.CreateCell(3).SetCellValue(val);
                            }
                            else if (kvp.Key == "town")
                            {
                                row.CreateCell(2).SetCellValue(val);
                            }
                            else if (kvp.Key == "xzq")
                            {
                                row.CreateCell(1).SetCellValue(val);
                            }
                            else if (kvp.Key == "hzxm")
                            {
                                row.CreateCell(4).SetCellValue(val);
                            }
                            else if (kvp.Key == "address")
                            {
                                row.CreateCell(5).SetCellValue(val);
                            }
                            else if (kvp.Key == "fwbh")
                            {
                                row.CreateCell(6).SetCellValue(val);
                            }

                        }

                        i++;
                    }
                }

                // 写 WorkBook信息到 内存流中
                byte[] buffer = null;
                using (MemoryStream ms = new MemoryStream())
                {
                    wb.Write(ms);
                    buffer = ms.ToArray();
                }
                return File(buffer, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "村民经营性自建房消防安全排查治理" + filename + ".xlsx");
            }
            catch (Exception ex)
            {
                throw;
            }

        }
        /// <summary>
        /// 统计拆除、不出租的房屋
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("village/evolvestatic/HouseRemoveList")]
        public RespRet<Page<object>> HouseRemoveList([FromQuery] string xzqdm, [FromQuery] string towndm, [FromQuery] string villagedm, [FromQuery] int pageIndex, [FromQuery] int limit, [FromQuery] string endTime, [FromQuery] string startTime, [FromQuery] string txtKeyWord, [FromQuery] string fwzt)
        {
            if (pageIndex < 0)
            {
                return new RespRet<Page<object>>(false, "PageIndex must >= 0");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }

            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<Page<object>>(false, "用户名不存在");
            }
            string strWhere = " 1=1 ";
            var sbSql = new StringBuilder();
            var sbSqlWhere = new StringBuilder();
            var sqlParams = new List<NpgsqlParameter>();

            var sbSqlBuildingEx = new StringBuilder();

            string district = user.District;

            var options = new JsonSerializerOptions();
            options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
            Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);

            if (user.RoleType == 1 || user.RoleType == 2)
            {
                strWhere += " and xzq!='东城区' and xzq!='西城区' ";
            }
            else if (user.RoleType == 3)  //区级
            {
                strWhere += " and xzqdm='" + model.RegionCode + "'";
            }
            else if (user.RoleType == 4)
            {
                string tdm = model.RegionCode;
                if (model.RegionCode.Length != 12)
                {
                    tdm = model.RegionCode + "000";
                }
                strWhere += " and towndm='" + tdm + "'";
            }
            else if (user.RoleType == 5)
            {
                strWhere += " and villagedm='" + model.RegionCode + "'";
            }


            if (!string.IsNullOrEmpty(txtKeyWord))
            {
                strWhere += " and (hzxm like '%" + txtKeyWord + "%' or fwbh like '%" + txtKeyWord + "%' or address like '%" + txtKeyWord + "%')";
            }


            if (!string.IsNullOrEmpty(xzqdm))
            {
                strWhere += " and xzqdm='" + xzqdm + "' ";
            }
            if (!string.IsNullOrEmpty(towndm))
            {
                strWhere += " and towndm='" + towndm + "'";
            }
            if (!string.IsNullOrEmpty(villagedm))
            {
                strWhere += " and villagedm='" + villagedm + "' ";
            }
            if (!string.IsNullOrEmpty(startTime) && !string.IsNullOrEmpty(endTime))
            {
                strWhere += " and  ( date(create_on) BETWEEN '" + startTime + "' and  '" + endTime + "')";
            }
            if (!string.IsNullOrEmpty(fwzt))
            {
                strWhere += " and  ( fwzt='" + fwzt + "')";
            }
            string today = DateTime.Now.ToString("yyyy-MM-dd");
            string table_fwzt = "";
            if (!string.IsNullOrEmpty(endTime))
            {
                DateTime endData = Convert.ToDateTime(endTime);
                table_fwzt = "zm_building_fwzt_" + endData.Year;
            }
            else
            {
                DateTime endData = Convert.ToDateTime(today);
                table_fwzt = "zm_building_fwzt_" + endData.Year;
            }
            string sql = @"  select ROW_NUMBER() OVER ()index,* from (select t1.xzqdm,t2.xzq,t1.towndm,t2.town,t1.villagedm,t2.village,t2.hzxm,t2.address,t1.fwbh,case when t1.final_fwzt =1 then '不出租' when t1.final_fwzt =2 then '已拆除' when t1.final_fwzt =3 then '已删除'  end  fwzt,final_create_on from " + table_fwzt + " t1 left join zm_buildings t2 on t1.fwbh=t2.fwbh where final_fwzt is not null and final_create_on='" + today + " 23:59:59' order by final_create_on desc) t where   " + strWhere + "limit " + limit + " offset(" + pageIndex + ") * " + limit;

            if (sql.Contains("insert into", StringComparison.OrdinalIgnoreCase)
          || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
          || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }

            //string sql_count = @" select Count(1) cnt from (select * from "+ table_fwzt + " order by final_create_on desc) t where  " + strWhere + " ";
            string sql_count = @"  select count(1) cnt from (select t1.xzqdm,t2.xzq,t1.towndm,t2.town,t1.villagedm,t2.village,t2.hzxm,
t2.address,t1.fwbh,case when t1.final_fwzt =1 then '不出租' when t1.final_fwzt =2 then '已拆除' end fwzt,final_create_on from " + table_fwzt + " t1" +
" left join zm_buildings t2 on t1.fwbh=t2.fwbh where final_fwzt is not null and final_create_on='" + today + " 23:59:59' order by final_create_on desc) t where   " + strWhere;

            if (sql_count.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            //执行语句
            var list = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            int count = _dbContext.Database.GetDbConnection().QueryFirstOrDefault<int>(sql_count);

            return new RespRet<Page<object>>(true, "ok", new Page<object> { Items = list, NextPageIndex = pageIndex, PageTotal = count, Total = count });

        }


        /// <summary>
        /// 统计拆除、不出租的房屋数据导出
        /// </summary>
        /// <param name="pageRequest">type check</param>
        /// <returns></returns>
        [HttpGet("village/evolvestatic/ExportHouseRemoveList")]
        [Produces("application/octet-stream")]
        //[ProducesResponseType(200)]
        //[ProducesResponseType(401)]
        public IActionResult ExportHouseRemoveList([FromQuery] string xzqdm, [FromQuery] string towndm, [FromQuery] string villagedm, [FromQuery] string txtKeyWord, [FromQuery] string fwzt)
        {
            try
            {
                //var list = new List<VillageFireSafetyAttrDto>();
                var userNumber = this.CurrentUser();

                var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
                var sbSql = new StringBuilder();
                var sbSqlWhere = new StringBuilder();
                var sqlParams = new List<NpgsqlParameter>();

                var sbSqlBuildingEx = new StringBuilder();

                string district = user.District;

                var options = new JsonSerializerOptions();
                options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
                Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);

                string strWhere = " where 1=1 ";

                if (user.RoleType == 1 || user.RoleType == 2)
                {
                    strWhere += " and xzq!='东城区' and xzq!='西城区' ";

                }
                else if (user.RoleType == 3)  //区级
                {
                    strWhere += " and xzqdm='" + model.RegionCode + "'";
                }
                else if (user.RoleType == 4)
                {
                    string tdm = model.RegionCode;
                    if (model.RegionCode.Length != 12)
                    {
                        tdm = model.RegionCode + "000";
                    }
                    strWhere += " and towndm='" + tdm + "'";
                }
                else if (user.RoleType == 5)
                {
                    strWhere += " and villagedm='" + model.RegionCode + "'";
                }

                if (!string.IsNullOrEmpty(txtKeyWord))
                {
                    strWhere += " and (hzxm like '%" + txtKeyWord + "%' or fwbh like '%" + txtKeyWord + "%' or address like '%" + txtKeyWord + "%')";
                }

                if (!string.IsNullOrEmpty(xzqdm))
                {
                    strWhere += " and xzqdm='" + xzqdm + "' ";
                }
                if (!string.IsNullOrEmpty(towndm))
                {
                    strWhere += " and towndm='" + towndm + "'";
                }
                if (!string.IsNullOrEmpty(villagedm))
                {
                    strWhere += " and villagedm='" + villagedm + "' ";
                }

                if (!string.IsNullOrEmpty(fwzt))
                {
                    strWhere += " and  ( fwzt='" + fwzt + "')";
                }
                string today = DateTime.Now.ToString("yyyy-MM-dd");
                string table_fwzt = "";

                DateTime endData = Convert.ToDateTime(today);
                table_fwzt = "zm_building_fwzt_" + endData.Year;
                string sql = @"select ROW_NUMBER() OVER ()index,* from (select t1.xzqdm,t2.xzq,t1.towndm,t2.town,t1.villagedm,t2.village,t2.hzxm,t2.address,
t1.fwbh,case when t1.final_fwzt =1 then '不出租' when t1.final_fwzt =2 then '已拆除'   when t1.final_fwzt =3 then '已删除'  end fwzt,final_create_on from " + table_fwzt+" t1"+
" left join zm_buildings t2 on t1.fwbh=t2.fwbh where final_fwzt is not null and final_create_on='" + today + " 23:59:59' order by final_create_on desc) t  " + strWhere;


                //写入缓存
                var data = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
                IWorkbook wb = new XSSFWorkbook();
                ISheet sheet = wb.CreateSheet(model.RegionName + "");
                // 第一行 标题
                IRow row = sheet.CreateRow(0);
                row.CreateCell(0).SetCellValue("序号");
                row.CreateCell(1).SetCellValue("区名称");
                row.CreateCell(2).SetCellValue("乡镇/街名称");
                row.CreateCell(3).SetCellValue("村/社区名称");
                row.CreateCell(4).SetCellValue("房主姓名");
                row.CreateCell(5).SetCellValue("地址");
                row.CreateCell(6).SetCellValue("房屋编号");
                row.CreateCell(7).SetCellValue("房屋状态");
                // 第二行 写数据
                int i = 1;
                foreach (object obj in data)
                {
                    Dictionary<string, object> dictionary = new Dictionary<string, object>();
                    if (obj != null)
                    {

                        var properties = obj as IDictionary<string, object>;
                        row = sheet.CreateRow(i);
                        foreach (var kvp in properties)
                        {
                            var val = kvp.Value + "";
                            if (kvp.Key == "index")
                            {
                                row.CreateCell(0).SetCellValue(i);
                            }
                            else if (kvp.Key == "village")
                            {
                                row.CreateCell(3).SetCellValue(val);
                            }
                            else if (kvp.Key == "town")
                            {
                                row.CreateCell(2).SetCellValue(val);
                            }
                            else if (kvp.Key == "xzq")
                            {
                                row.CreateCell(1).SetCellValue(val);
                            }
                            else if (kvp.Key == "hzxm")
                            {
                                row.CreateCell(4).SetCellValue(val);
                            }
                            else if (kvp.Key == "address")
                            {
                                row.CreateCell(5).SetCellValue(val);
                            }
                            else if (kvp.Key == "fwbh")
                            {
                                row.CreateCell(6).SetCellValue(val);
                            }
                            else if (kvp.Key == "fwzt")
                            {
                                row.CreateCell(7).SetCellValue(val);
                            }
                        }

                        i++;
                    }
                }

                // 写 WorkBook信息到 内存流中
                byte[] buffer = null;
                using (MemoryStream ms = new MemoryStream())
                {
                    wb.Write(ms);
                    buffer = ms.ToArray();
                }
                return File(buffer, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "村民经营性自建房消防安全排查治理不出租、已拆除房屋-" + DateTime.Now.ToString("yyyymmdd") + ".xlsx");
            }
            catch (Exception ex)
            {
                throw;
            }

        }



        /// <summary>
        /// 统计住建房屋
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("village/evolvestatic/ZJHouseList")]
        public RespRet<Page<object>> ZJHouseList([FromQuery] string xzqdm, [FromQuery] string towndm, [FromQuery] string villagedm, [FromQuery] int pageIndex, [FromQuery] int limit,  [FromQuery] string txtKeyWord)
        {
            if (pageIndex < 0)
            {
                return new RespRet<Page<object>>(false, "PageIndex must >= 0");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }

            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<Page<object>>(false, "用户名不存在");
            }
            string strWhere = " 1=1 ";
            var sbSql = new StringBuilder();
            var sbSqlWhere = new StringBuilder();
            var sqlParams = new List<NpgsqlParameter>();

            var sbSqlBuildingEx = new StringBuilder();

            string district = user.District;

            var options = new JsonSerializerOptions();
            options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
            Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);

            if (user.RoleType == 1 || user.RoleType == 2)
            {
                strWhere += " and xzq!='东城区' and xzq!='西城区' ";
            }
            else if (user.RoleType == 3)  //区级
            {
                strWhere += " and xzqdm='" + model.RegionCode + "'";
            }
            else if (user.RoleType == 4)
            {
                string tdm = model.RegionCode;
                if (model.RegionCode.Length != 12)
                {
                    tdm = model.RegionCode + "000";
                }
                strWhere += " and towndm='" + tdm + "'";
            }
            else if (user.RoleType == 5)
            {
                strWhere += " and villagedm='" + model.RegionCode + "'";
            }


            if (!string.IsNullOrEmpty(txtKeyWord))
            {
                strWhere += " and (hzxm like '%" + txtKeyWord + "%' or fwbh like '%" + txtKeyWord + "%' or address like '%" + txtKeyWord + "%')";
            }


            if (!string.IsNullOrEmpty(xzqdm))
            {
                strWhere += " and xzqdm='" + xzqdm + "' ";
            }
            if (!string.IsNullOrEmpty(towndm))
            {
                strWhere += " and towndm='" + towndm + "'";
            }
            if (!string.IsNullOrEmpty(villagedm))
            {
                strWhere += " and villagedm='" + villagedm + "' ";
            }
           
            string sql = @"  select ROW_NUMBER() OVER ()index,* from (select xzq,xzqdm,towndm,town,villagedm,village,hzxm,address,fwbh  from zm_buildings_zj ) t where   " + strWhere + "limit " + limit + " offset(" + pageIndex + ") * " + limit;

            if (sql.Contains("insert into", StringComparison.OrdinalIgnoreCase)
          || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
          || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }

            //string sql_count = @" select Count(1) cnt from (select * from "+ table_fwzt + " order by final_create_on desc) t where  " + strWhere + " ";
            string sql_count = @"  select count(1) cnt from  zm_buildings_zj  where   " + strWhere;

            if (sql_count.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            //执行语句
            var list = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            int count = _dbContext.Database.GetDbConnection().QueryFirstOrDefault<int>(sql_count);

            return new RespRet<Page<object>>(true, "ok", new Page<object> { Items = list, NextPageIndex = pageIndex, PageTotal = count, Total = count });

        }




        /// <summary>
        /// 隐患整改情况
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("village/evolvestatic/YHZGHouseList")]
        public RespRet<Page<object>> YHZGHouseList([FromQuery] string xzqdm, [FromQuery] string towndm, [FromQuery] string villagedm, [FromQuery] int pageIndex, [FromQuery] int limit, [FromQuery] string txtKeyWord, [FromQuery] string yhly, [FromQuery] string checkname)
        {
            if (pageIndex < 0)
            {
                return new RespRet<Page<object>>(false, "PageIndex must >= 0");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }

            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<Page<object>>(false, "用户名不存在");
            }
            string strWhere = " 1=1 ";
            var sbSql = new StringBuilder();
            var sbSqlWhere = new StringBuilder();
            var sqlParams = new List<NpgsqlParameter>();

            var sbSqlBuildingEx = new StringBuilder();

            string district = user.District;

            var options = new JsonSerializerOptions();
            options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
            Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);

          
            string today = DateTime.Now.ToString("yyyy-MM-dd");
            DateTime endData = Convert.ToDateTime(today);
            string table_fwzt = "zm_building_fwzt_" + endData.Year;
            strWhere += "  and fwbh not in (select distinct fwbh from " + table_fwzt + " where  (final_fwzt IS NOT NULL) and (final_create_on = '"+ today + " 23:59:59' ))";
            if (user.RoleType == 1 || user.RoleType == 2)
            {
                strWhere += " and xzq!='东城区' and xzq!='西城区' ";
            }
            else if (user.RoleType == 3)  //区级
            {
                strWhere += " and xzqdm='" + model.RegionCode + "'";
            }
            else if (user.RoleType == 4)
            {
                string tdm = model.RegionCode;
                if (model.RegionCode.Length != 12)
                {
                    tdm = model.RegionCode + "000";
                }
                strWhere += " and towndm='" + tdm + "'";
            }
            else if (user.RoleType == 5)
            {
                strWhere += " and villagedm='" + model.RegionCode + "'";
            }


            if (!string.IsNullOrEmpty(txtKeyWord))
            {
                strWhere += " and (hzxm like '%" + txtKeyWord + "%' or fwbh like '%" + txtKeyWord + "%' or address like '%" + txtKeyWord + "%')";
            }

            

            if (!string.IsNullOrEmpty(xzqdm))
            {
                strWhere += " and xzqdm='" + xzqdm + "' ";
            }
            if (!string.IsNullOrEmpty(towndm))
            {
                strWhere += " and towndm='" + towndm + "'";
            }
            if (!string.IsNullOrEmpty(villagedm))
            {
                strWhere += " and villagedm='" + villagedm + "' ";
            }

            if (!string.IsNullOrEmpty(yhly))
            {
                strWhere += " and yhly='" + yhly + "' ";
            }
            if (!string.IsNullOrEmpty(checkname))
            {
                if (checkname.Equals("其他隐患"))
                {
                    strWhere += " and check_name!='未按标准建设疏散通道' ";
                }
                else
                { 
                  strWhere += " and check_name='" + checkname + "' ";
                }
            }
          

            string sql = @"  select ROW_NUMBER() OVER ()index,* from (select * from (SELECT
	t1.xzq,
	t1.xzqdm,
	t1.towndm,
	t1.town,
	t1.villagedm,
	t1.village,
	t1.fwbh,
	t3.address,
    t3.hzxm,
	t1.check_name,
t2.check_nickname,   
t2.check_time, 
	t1.zg_zp,
    t1.zg_name,
	case when t2.jcly='3' then '区级' when t2.jcly='2' then '市级'   else '乡镇' end yhly,
	case when  t2.zg_value='1' then '已整改'    else '未整改' end zg_value,
	t1.fh_name,
	t1.fh_time,
	case when  t1.jczt='1' then '待复核'  when  t1.jczt='2' then '复核通过'  else '待处理' end jczt,
	
	case when  t1.fh_zt_cj='0' then '村级复核通过' when t1.fh_zt_cj='1' then '村级复核未通过'  else '' end fh_zt_cj,
	
	t1.fh_name_cj,
	t1.fh_time_cj,
	
	case when  t1.fh_zt_xz='0' then '乡镇复核通过' when t1.fh_zt_xz='1' then '乡镇复核未通过'  else '' end fh_zt_xz,
	t1.fh_name_xz,
	t1.fh_time_xz,
	
	case when  t1.fh_zt_qj='0' then '区级复核通过' when t1.fh_zt_qj='1' then '区级复核通过'  else '' end fh_zt_qj,
	t1.fh_name_qj,
	t1.fh_time_qj
	
FROM
	zm_pc_yhlog_dxzgson_sqjz   t1
	LEFT JOIN zm_pc_yhlog_dx_sqjz t2 ON t1.yhlog_dx_id = t2.id
	left join zm_buildings t3 on t1.fwbh=t3.fwbh
	
	union all
	
	SELECT
	t1.xzq,
	t1.xzqdm,
	t1.towndm,
	t1.town,
	t1.villagedm,
	t1.village,
	t1.fwbh,
    t3.address,     
    t3.hzxm,
t1.check_name,
t2.check_nickname,
t2.check_time, 
	t1.zg_zp,
	t1.zg_name,
	 '村级'  yhly,
	case when  t2.zg_value='1' then '已整改'    else '未整改' end zg_value,
	
	t1.fh_name,
	t1.fh_time,
	case when  t1.jczt='1' then '待复核'  when  t1.jczt='2' then '复核通过'  else '待处理' end jczt,
	
	case when  t1.fh_zt_cj='0' then '村级复核通过' when t1.fh_zt_cj='1' then '村级复核未通过'  else '' end fh_zt_cj,
	
	t1.fh_name_cj,
	t1.fh_time_cj,
	
	case when  t1.fh_zt_xz='0' then '乡镇复核通过' when t1.fh_zt_xz='1' then '乡镇复核未通过'  else '' end fh_zt_xz,
	t1.fh_name_xz,
	t1.fh_time_xz,
	
	case when  t1.fh_zt_qj='0' then '区级复核通过' when t1.fh_zt_qj='1' then '区级复核通过'  else '' end fh_zt_qj,
	t1.fh_name_qj,
	t1.fh_time_qj
	
FROM
	zm_pc_yhlog_dxzgson_cj t1
	LEFT JOIN zm_pc_yhlog_dx_cj  t2 ON t1.yhlog_dx_id = t2.id
	left join zm_buildings t3 on t1.fwbh=t3.fwbh) t1  order by xzqdm,towndm,villagedm  ) t where   " + strWhere + "limit " + limit + " offset(" + pageIndex + ") * " + limit;

            if (sql.Contains("insert into", StringComparison.OrdinalIgnoreCase)
          || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
          || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }

            //string sql_count = @" select Count(1) cnt from (select * from "+ table_fwzt + " order by final_create_on desc) t where  " + strWhere + " ";
            string sql_count = @"  select count(1) cnt from  (SELECT
	t1.xzq,
	t1.xzqdm,
	t1.towndm,
	t1.town,
	t1.villagedm,
	t1.village,
	t1.fwbh,
	t3.address,
    t3.hzxm,
	t1.check_name,
    t2.check_nickname,
	t1.zg_zp,
    t1.zg_name,
	case when t2.jcly='3' then '区级' when t2.jcly='2' then '市级'   else '乡镇' end yhly,
	case when  t2.zg_value='1' then '已整改'    else '未整改' end zg_value,
	t1.fh_name,
	t1.fh_time,
	case when  t1.jczt='1' then '待复核'  when  t1.jczt='2' then '复核通过'  else '待处理' end jczt,
	
	case when  t1.fh_zt_cj='0' then '村级复核通过' when t1.fh_zt_cj='1' then '村级复核未通过'  else '' end fh_zt_cj,
	
	t1.fh_name_cj,
	t1.fh_time_cj,
	
	case when  t1.fh_zt_xz='0' then '乡镇复核通过' when t1.fh_zt_xz='1' then '乡镇复核未通过'  else '' end fh_zt_xz,
	t1.fh_name_xz,
	t1.fh_time_xz,
	
	case when  t1.fh_zt_qj='0' then '区级复核通过' when t1.fh_zt_qj='1' then '区级复核通过'  else '' end fh_zt_qj,
	t1.fh_name_qj,
	t1.fh_time_qj
	
FROM
	zm_pc_yhlog_dxzgson_sqjz   t1
	LEFT JOIN zm_pc_yhlog_dx_sqjz t2 ON t1.yhlog_dx_id = t2.id
	left join zm_buildings t3 on t1.fwbh=t3.fwbh
	
	union all
	
	SELECT
	t1.xzq,
	t1.xzqdm,
	t1.towndm,
	t1.town,
	t1.villagedm,
	t1.village,
	t1.fwbh,
    t3.address,     
    t3.hzxm,
t1.check_name,
t2.check_nickname,
	t1.zg_zp,
	t1.zg_name,
	 '村级'  yhly,
	case when  t2.zg_value='1' then '已整改'    else '未整改' end zg_value,
	
	t1.fh_name,
	t1.fh_time,
	case when  t1.jczt='1' then '待复核'  when  t1.jczt='2' then '复核通过'  else '待处理' end jczt,
	
	case when  t1.fh_zt_cj='0' then '村级复核通过' when t1.fh_zt_cj='1' then '村级复核未通过'  else '' end fh_zt_cj,
	
	t1.fh_name_cj,
	t1.fh_time_cj,
	
	case when  t1.fh_zt_xz='0' then '乡镇复核通过' when t1.fh_zt_xz='1' then '乡镇复核未通过'  else '' end fh_zt_xz,
	t1.fh_name_xz,
	t1.fh_time_xz,
	
	case when  t1.fh_zt_qj='0' then '区级复核通过' when t1.fh_zt_qj='1' then '区级复核通过'  else '' end fh_zt_qj,
	t1.fh_name_qj,
	t1.fh_time_qj
	
FROM
	zm_pc_yhlog_dxzgson_cj t1
	LEFT JOIN zm_pc_yhlog_dx_cj  t2 ON t1.yhlog_dx_id = t2.id
	left join zm_buildings t3 on t1.fwbh=t3.fwbh)  t1 where   " + strWhere;

            if (sql_count.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            //执行语句
            var list = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            int count = _dbContext.Database.GetDbConnection().QueryFirstOrDefault<int>(sql_count);

            return new RespRet<Page<object>>(true, "ok", new Page<object> { Items = list, NextPageIndex = pageIndex, PageTotal = count, Total = count });

        }



        /// <summary>
        /// 统计住建房屋
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("village/evolvestatic/WZCHouseList")]
        public RespRet<Page<object>> WZCHouseList([FromQuery] string xzqdm, [FromQuery] string towndm, [FromQuery] string villagedm, [FromQuery] int pageIndex, [FromQuery] int limit, [FromQuery] string txtKeyWord)
        {
            if (pageIndex < 0)
            {
                return new RespRet<Page<object>>(false, "PageIndex must >= 0");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }

            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<Page<object>>(false, "用户名不存在");
            }
            string strWhere = " 1=1 ";
            var sbSql = new StringBuilder();
            var sbSqlWhere = new StringBuilder();
            var sqlParams = new List<NpgsqlParameter>();

            var sbSqlBuildingEx = new StringBuilder();

            string district = user.District;

            var options = new JsonSerializerOptions();
            options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
            Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);

            if (user.RoleType == 1 || user.RoleType == 2)
            {
                strWhere += " and xzq!='东城区' and xzq!='西城区' ";
            }
            else if (user.RoleType == 3)  //区级
            {
                strWhere += " and xzqdm='" + model.RegionCode + "'";
            }
            else if (user.RoleType == 4)
            {
                string tdm = model.RegionCode;
                if (model.RegionCode.Length != 12)
                {
                    tdm = model.RegionCode + "000";
                }
                strWhere += " and towndm='" + tdm + "'";
            }
            else if (user.RoleType == 5)
            {
                strWhere += " and villagedm='" + model.RegionCode + "'";
            }


            if (!string.IsNullOrEmpty(txtKeyWord))
            {
                strWhere += " and (hzxm like '%" + txtKeyWord + "%' or fwbh like '%" + txtKeyWord + "%' or address like '%" + txtKeyWord + "%')";
            }

            string time = DateTime.Today
                         .AddHours(23)
                         .AddMinutes(59)
                         .AddSeconds(59)
                         .ToString("yyyy-MM-dd HH:mm:ss");
            string table_name = "zm_building_fwzt_" + DateTime.Now.Year;

            DateTime today = DateTime.Today;

            var prevMonth = new DateTime(today.Year, today.Month, 1).AddMonths(-1);
            var year = prevMonth.Year;
            var month = prevMonth.ToString("MM");
            string tablename = "zm_pc_baselog_" + year + "_" + month;


            if (!string.IsNullOrEmpty(xzqdm))
            {
                strWhere += " and xzqdm='" + xzqdm + "' ";
            }
            if (!string.IsNullOrEmpty(towndm))
            {
                strWhere += " and towndm='" + towndm + "'";
            }
            if (!string.IsNullOrEmpty(villagedm))
            {
                strWhere += " and villagedm='" + villagedm + "' ";
            }

            string sql = @"  select ROW_NUMBER() OVER ()index,* from (select xzq,xzqdm,towndm,town,villagedm,village,hzxm,address,fwbh  from zm_buildings  where fwbh not in (select fwbh from  "+ table_name + " where final_create_on='" + time + "' and final_fwzt is not null )  and  fwbh not in (select distinct(fwbh) from  "+ tablename + ")) t where   " + strWhere + "limit " + limit + " offset(" + pageIndex + ") * " + limit;

            if (sql.Contains("insert into", StringComparison.OrdinalIgnoreCase)
          || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
          || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }

            //string sql_count = @" select Count(1) cnt from (select * from "+ table_fwzt + " order by final_create_on desc) t where  " + strWhere + " ";
            string sql_count = @" select count(1)  from (select xzq,xzqdm,towndm,town,villagedm,village,hzxm,address,fwbh  from zm_buildings  where fwbh not in (select fwbh from  "+ table_name + " where final_create_on='" + time + "' and final_fwzt is not null )  and  fwbh not in (select distinct(fwbh) from  "+ tablename + ")) t where   " + strWhere;

            if (sql_count.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            //执行语句
            var list = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            int count = _dbContext.Database.GetDbConnection().QueryFirstOrDefault<int>(sql_count);

            return new RespRet<Page<object>>(true, "ok", new Page<object> { Items = list, NextPageIndex = pageIndex, PageTotal = count, Total = count });

        }



        /// <summary>
        /// 统计住建房屋数据导出
        /// </summary>
        /// <param name="pageRequest">type check</param>
        /// <returns></returns>
        [HttpGet("village/evolvestatic/ExportZJHouseList")]
        [Produces("application/octet-stream")]
        //[ProducesResponseType(200)]
        //[ProducesResponseType(401)]
        public IActionResult ExportZJHouseList([FromQuery] string xzqdm, [FromQuery] string towndm, [FromQuery] string villagedm, [FromQuery] string txtKeyWord)
        {
            try
            {
                //var list = new List<VillageFireSafetyAttrDto>();
                var userNumber = this.CurrentUser();

                var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
                var sbSql = new StringBuilder();
                var sbSqlWhere = new StringBuilder();
                var sqlParams = new List<NpgsqlParameter>();

                var sbSqlBuildingEx = new StringBuilder();

                string district = user.District;

                var options = new JsonSerializerOptions();
                options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
                Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);

                string strWhere = " where 1=1 ";

                if (user.RoleType == 1 || user.RoleType == 2)
                {
                    strWhere += " and xzq!='东城区' and xzq!='西城区' ";

                }
                else if (user.RoleType == 3)  //区级
                {
                    strWhere += " and xzqdm='" + model.RegionCode + "'";
                }
                else if (user.RoleType == 4)
                {
                    string tdm = model.RegionCode;
                    if (model.RegionCode.Length != 12)
                    {
                        tdm = model.RegionCode + "000";
                    }
                    strWhere += " and towndm='" + tdm + "'";
                }
                else if (user.RoleType == 5)
                {
                    strWhere += " and villagedm='" + model.RegionCode + "'";
                }

                if (!string.IsNullOrEmpty(txtKeyWord))
                {
                    strWhere += " and (hzxm like '%" + txtKeyWord + "%' or fwbh like '%" + txtKeyWord + "%' or address like '%" + txtKeyWord + "%')";
                }

                if (!string.IsNullOrEmpty(xzqdm))
                {
                    strWhere += " and xzqdm='" + xzqdm + "' ";
                }
                if (!string.IsNullOrEmpty(towndm))
                {
                    strWhere += " and towndm='" + towndm + "'";
                }
                if (!string.IsNullOrEmpty(villagedm))
                {
                    strWhere += " and villagedm='" + villagedm + "' ";
                }

             
               
                string sql = @"select ROW_NUMBER() OVER ()index,* from (select xzq,xzqdm,towndm,town,villagedm,village,hzxm,address,fwbh  from zm_buildings_zj) t  " + strWhere;


                //写入缓存
                var data = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
                IWorkbook wb = new XSSFWorkbook();
                ISheet sheet = wb.CreateSheet(model.RegionName + "");
                // 第一行 标题
                IRow row = sheet.CreateRow(0);
                row.CreateCell(0).SetCellValue("序号");
                row.CreateCell(1).SetCellValue("区名称");
                row.CreateCell(2).SetCellValue("乡镇/街名称");
                row.CreateCell(3).SetCellValue("村/社区名称");
                row.CreateCell(4).SetCellValue("房主姓名");
                row.CreateCell(5).SetCellValue("地址");
                row.CreateCell(6).SetCellValue("房屋编号");
                // 第二行 写数据
                int i = 1;
                foreach (object obj in data)
                {
                    Dictionary<string, object> dictionary = new Dictionary<string, object>();
                    if (obj != null)
                    {

                        var properties = obj as IDictionary<string, object>;
                        row = sheet.CreateRow(i);
                        foreach (var kvp in properties)
                        {
                            var val = kvp.Value + "";
                            if (kvp.Key == "index")
                            {
                                row.CreateCell(0).SetCellValue(i);
                            }
                            else if (kvp.Key == "village")
                            {
                                row.CreateCell(3).SetCellValue(val);
                            }
                            else if (kvp.Key == "town")
                            {
                                row.CreateCell(2).SetCellValue(val);
                            }
                            else if (kvp.Key == "xzq")
                            {
                                row.CreateCell(1).SetCellValue(val);
                            }
                            else if (kvp.Key == "hzxm")
                            {
                                row.CreateCell(4).SetCellValue(val);
                            }
                            else if (kvp.Key == "address")
                            {
                                row.CreateCell(5).SetCellValue(val);
                            }
                            else if (kvp.Key == "fwbh")
                            {
                                row.CreateCell(6).SetCellValue(val);
                            }
                        }

                        i++;
                    }
                }

                // 写 WorkBook信息到 内存流中
                byte[] buffer = null;
                using (MemoryStream ms = new MemoryStream())
                {
                    wb.Write(ms);
                    buffer = ms.ToArray();
                }
                return File(buffer, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "村民经营性自建房消防安全排查治理住建房屋-" + DateTime.Now.ToString("yyyymmdd") + ".xlsx");
            }
            catch (Exception ex)
            {
                throw;
            }

        }




        /// <summary>
        /// 上个月未自查房屋导出
        /// </summary>
        /// <param name="pageRequest">type check</param>
        /// <returns></returns>
        [HttpGet("village/evolvestatic/ExportWZCHouseList")]
        [Produces("application/octet-stream")]
        //[ProducesResponseType(200)]
        //[ProducesResponseType(401)]
        public IActionResult ExportWZCHouseList([FromQuery] string xzqdm, [FromQuery] string towndm, [FromQuery] string villagedm, [FromQuery] string txtKeyWord)
        {
            try
            {
                //var list = new List<VillageFireSafetyAttrDto>();
                var userNumber = this.CurrentUser();

                var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
                var sbSql = new StringBuilder();
                var sbSqlWhere = new StringBuilder();
                var sqlParams = new List<NpgsqlParameter>();

                var sbSqlBuildingEx = new StringBuilder();

                string district = user.District;

                var options = new JsonSerializerOptions();
                options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
                Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);

                string strWhere = "  1=1 ";

                if (user.RoleType == 1 || user.RoleType == 2)
                {
                    strWhere += " and xzq!='东城区' and xzq!='西城区' ";

                }
                else if (user.RoleType == 3)  //区级
                {
                    strWhere += " and xzqdm='" + model.RegionCode + "'";
                }
                else if (user.RoleType == 4)
                {
                    string tdm = model.RegionCode;
                    if (model.RegionCode.Length != 12)
                    {
                        tdm = model.RegionCode + "000";
                    }
                    strWhere += " and towndm='" + tdm + "'";
                }
                else if (user.RoleType == 5)
                {
                    strWhere += " and villagedm='" + model.RegionCode + "'";
                }

                if (!string.IsNullOrEmpty(txtKeyWord))
                {
                    strWhere += " and (hzxm like '%" + txtKeyWord + "%' or fwbh like '%" + txtKeyWord + "%' or address like '%" + txtKeyWord + "%')";
                }

                if (!string.IsNullOrEmpty(xzqdm))
                {
                    strWhere += " and xzqdm='" + xzqdm + "' ";
                }
                if (!string.IsNullOrEmpty(towndm))
                {
                    strWhere += " and towndm='" + towndm + "'";
                }
                if (!string.IsNullOrEmpty(villagedm))
                {
                    strWhere += " and villagedm='" + villagedm + "' ";
                }



                string time = DateTime.Today
                     .AddHours(23)
                     .AddMinutes(59)
                     .AddSeconds(59)
                     .ToString("yyyy-MM-dd HH:mm:ss");
                string table_name = "zm_building_fwzt_" + DateTime.Now.Year;

                DateTime today = DateTime.Today;

                var prevMonth = new DateTime(today.Year, today.Month, 1).AddMonths(-1);
                var year = prevMonth.Year;
                var month = prevMonth.ToString("MM");
                string tablename = "zm_pc_baselog_" + year + "_" + month;


                if (!string.IsNullOrEmpty(xzqdm))
                {
                    strWhere += " and xzqdm='" + xzqdm + "' ";
                }
                if (!string.IsNullOrEmpty(towndm))
                {
                    strWhere += " and towndm='" + towndm + "'";
                }
                if (!string.IsNullOrEmpty(villagedm))
                {
                    strWhere += " and villagedm='" + villagedm + "' ";
                }

                string sql = @"  select ROW_NUMBER() OVER ()index,* from (select xzq,xzqdm,towndm,town,villagedm,village,hzxm,address,fwbh  from zm_buildings  where fwbh not in (select fwbh from  " + table_name + " where final_create_on='" + time + "' and final_fwzt is not null )  and  fwbh not in (select distinct(fwbh) from  " + tablename + ")) t where   " + strWhere;


                //写入缓存
                var data = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
                IWorkbook wb = new XSSFWorkbook();
                ISheet sheet = wb.CreateSheet(model.RegionName + "");
                // 第一行 标题
                IRow row = sheet.CreateRow(0);
                row.CreateCell(0).SetCellValue("序号");
                row.CreateCell(1).SetCellValue("区名称");
                row.CreateCell(2).SetCellValue("乡镇/街名称");
                row.CreateCell(3).SetCellValue("村/社区名称");
                row.CreateCell(4).SetCellValue("房主姓名");
                row.CreateCell(5).SetCellValue("地址");
                row.CreateCell(6).SetCellValue("房屋编号");
                // 第二行 写数据
                int i = 1;
                foreach (object obj in data)
                {
                    Dictionary<string, object> dictionary = new Dictionary<string, object>();
                    if (obj != null)
                    {

                        var properties = obj as IDictionary<string, object>;
                        row = sheet.CreateRow(i);
                        foreach (var kvp in properties)
                        {
                            var val = kvp.Value + "";
                            if (kvp.Key == "index")
                            {
                                row.CreateCell(0).SetCellValue(i);
                            }
                            else if (kvp.Key == "village")
                            {
                                row.CreateCell(3).SetCellValue(val);
                            }
                            else if (kvp.Key == "town")
                            {
                                row.CreateCell(2).SetCellValue(val);
                            }
                            else if (kvp.Key == "xzq")
                            {
                                row.CreateCell(1).SetCellValue(val);
                            }
                            else if (kvp.Key == "hzxm")
                            {
                                row.CreateCell(4).SetCellValue(val);
                            }
                            else if (kvp.Key == "address")
                            {
                                row.CreateCell(5).SetCellValue(val);
                            }
                            else if (kvp.Key == "fwbh")
                            {
                                row.CreateCell(6).SetCellValue(val);
                            }
                        }

                        i++;
                    }
                }

                // 写 WorkBook信息到 内存流中
                byte[] buffer = null;
                using (MemoryStream ms = new MemoryStream())
                {
                    wb.Write(ms);
                    buffer = ms.ToArray();
                }
                return File(buffer, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "村民经营性自建房上个月未自查房屋清单-" + DateTime.Now.ToString("yyyymmdd") + ".xlsx");
            }
            catch (Exception ex)
            {
                throw;
            }

        }


        /// <summary>
        /// 获取当前区所有的用户
        /// </summary>
        /// <param name="pageindex"></param>
        /// <param name="pagesize"></param>
        /// <param name="txtKeyWord"></param>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <returns></returns>
        [HttpGet("village/evolvestatic/getCheckUserList")]
        public RespRet<Page<object>> getCheckUserList([FromQuery] int pageindex, [FromQuery] int pagesize, [FromQuery] string xzq, [FromQuery] string txtKeyWord, [FromQuery] string startTime, [FromQuery] string endTime, [FromQuery] string category)
        {
            if (pageindex < 0)
            {
                return new RespRet<Page<object>>(false, "PageIndex must >= 0");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }
            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<Page<object>>(false, "用户名不存在");
            }
            string district = user.District;

            var options = new JsonSerializerOptions();
            options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
            Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);

            string strWhere = "  ";
            if (user.RoleType == 3)  //区级
            {
                strWhere += " and (xzqdm ='" + model.RegionCode + "')";
            }
            else if (user.RoleType == 4)
            {
                string tdm = model.RegionCode;
                if (model.RegionCode.Length != 12)
                {
                    tdm = model.RegionCode + "000";
                }
                strWhere += " and towndm='" + tdm + "'";
            }
            else if (user.RoleType == 5)
            {
                strWhere += " and (villagedm::text like '%" + model.RegionCode + "%')";
            }

            if (!string.IsNullOrEmpty(xzq))
            {
                strWhere += " and (xzq like '%" + xzq + "%')";
            }
            if (!string.IsNullOrEmpty(category))
            {
                strWhere += " and (role_type = '" + category + "')";
            }

            if (!string.IsNullOrEmpty(txtKeyWord))
            {
                strWhere += " and (nickname like '%" + txtKeyWord + "%')";
            }
            if (string.IsNullOrEmpty(startTime) && string.IsNullOrEmpty(endTime))
            {
                startTime = DateTime.Now.ToShortDateString();
                endTime = DateTime.Now.ToShortDateString();
            }

            string sql = "select ROW_NUMBER() OVER ()index,* from (select t1.*, COALESCE(housecount,0) housecount from (select *,case when (village is null or village ='') and (town is null or town ='')   then CONCAT(xzq) else case when (village is null or village ='') and (town is not  null or town !='') then  CONCAT(xzq,'-' ,town)  else CONCAT(xzq,'-' ,town,'-',village)  end   end AS district  from (select number, nickname,(select region_name xzq from zm_department where id =(SUBSTRING(district ->> 'RegionCode' ,0,7))),case when role_type='4' then '' else (select region_name town from zm_department where id =concat(SUBSTRING(district ->> 'RegionCode' ,0,10),'000')) end , case when role_type='3' then '' else  (select region_name village from zm_department where id =district ->> 'RegionCode') end,case when role_type='5' then   '村级用户'  else case when role_type='4' then '乡镇用户' else case when role_type='3'  then '区级用户' else  '市级用户' end end end role_type,to_char(t1.create_on::timestamp(0) without time zone, 'YYYY-MM-DD HH24:MI:SS') create_on,t1.create_ip,qaakey,district gxfw from zm_user_cj t1 ) t1  order by create_on desc,village asc) t1 left join \r\n\r\n(select count(1) housecount,qaakey,case when role_type='5' then   '村级用户'  else case when role_type='4' then '乡镇用户' else case when role_type='3'  then '区级用户' else  '市级用户' end end end role_type from  (select t1.xzq,t2.town,t2.village,t1.hzxm, t1.fwbh, address,case when  t1.fwzt is null  then '正常出租' else case when t1.fwzt='1' then '不出租' else '已拆除' end  end fwzt,t2.create_by,to_char(t2.create_on::timestamp(0) without time zone, 'YYYY-MM-DD HH24:MI:SS')   pcsj from zm_pc_base_cj t1,zm_pc_yh_cj t2 where t1.id=t2.base_id and t1.fwzt is null ) mm left join zm_user_cj nn on mm.create_by=nn.\"number\"  group by qaakey,role_type) t2 on t1.qaakey=t2.qaakey and t1.role_type=t2.role_type) t   " +
                " where 1 = 1   " + strWhere + "  limit " + pagesize + " offset (" + pageindex + " - 1) * " + pagesize + "";
            if (sql.Contains("insert    into", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            string sql_count = "select count(1) cnt from (select t1.*, COALESCE(housecount,0) housecount from (select *,case when (village is null or village ='') and (town is null or town ='')   then CONCAT(xzq) else case when (village is null or village ='') and (town is not  null or town !='') then  CONCAT(xzq,'-' ,town)  else CONCAT(xzq,'-' ,town,'-',village)  end   end AS district  from (select number, nickname,(select region_name xzq from zm_department where id =(SUBSTRING(district ->> 'RegionCode' ,0,7))),case when role_type='4' then '' else (select region_name town from zm_department where id =concat(SUBSTRING(district ->> 'RegionCode' ,0,10),'000')) end , case when role_type='3' then '' else  (select region_name village from zm_department where id =district ->> 'RegionCode') end,case when role_type='5' then   '村级用户'  else case when role_type='4' then '乡镇用户' else case when role_type='3'  then '区级用户' else  '市级用户' end end end role_type,to_char(t1.create_on::timestamp(0) without time zone, 'YYYY-MM-DD HH24:MI:SS') create_on,t1.create_ip,qaakey,district gxfw from zm_user_cj t1 ) t1  order by create_on desc,village asc) t1 left join \r\n\r\n(select count(1) housecount,qaakey,case when role_type='5' then   '村级用户'  else case when role_type='4' then '乡镇用户' else case when role_type='3'  then '区级用户' else  '市级用户' end end end role_type from  (select t1.xzq,t2.town,t2.village,t1.hzxm, t1.fwbh, address,case when  t1.fwzt is null  then '正常出租' else case when t1.fwzt='1' then '不出租' else '已拆除' end  end fwzt,t2.create_by,to_char(t2.create_on::timestamp(0) without time zone, 'YYYY-MM-DD HH24:MI:SS')   pcsj from zm_pc_base_cj t1,zm_pc_yh_cj t2 where t1.id=t2.base_id and t1.fwzt is null ) mm left join zm_user_cj nn on mm.create_by=nn.\"number\"  group by qaakey,role_type) t2 on t1.qaakey=t2.qaakey and t1.role_type=t2.role_type) t  where 1 = 1   " + strWhere + " ";
            if (sql_count.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }


            int count = _dbContext.Database.GetDbConnection().QueryFirstOrDefault<int>(sql_count);
            var datas = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            return new RespRet<Page<object>>(true, "ok", new Page<object> { Items = datas, NextPageIndex = pageindex + 1, PageTotal = count, Total = count });

        }
        /// <summary>
        /// 获取当前区某个检查人员所有已检查的房屋
        /// </summary>
        /// <param name="pageindex"></param>
        /// <param name="pagesize"></param>
        /// <param name="txtKeyWord"></param>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <returns></returns>
        [HttpGet("village/evolvestatic/getUserCheckHourseList")]
        public RespRet<Page<object>> getUserCheckHourseList([FromQuery] int pageindex, [FromQuery] int pagesize, [FromQuery] string xzq, [FromQuery] string txtKeyWord, [FromQuery] string startTime, [FromQuery] string endTime, [FromQuery] string qaakey)
        {
            if (pageindex < 0)
            {
                return new RespRet<Page<object>>(false, "PageIndex must >= 0");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }
            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<Page<object>>(false, "用户名不存在");
            }
            string district = user.District;

            var options = new JsonSerializerOptions();
            options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
            Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);

            string strWhere = "  ";
            if (user.RoleType == 3)  //区级
            {
                strWhere += " and (xzqdm ='" + model.RegionCode + "')";
            }
            else if (user.RoleType == 4)
            {
                string tdm = model.RegionCode;
                if (model.RegionCode.Length != 12)
                {
                    tdm = model.RegionCode + "000";
                }
                strWhere += " and towndm='" + tdm + "'";
            }
            else if (user.RoleType == 5)
            {
                strWhere += " and (villagedm::text like '%" + model.RegionCode + "%')";
            }

            if (!string.IsNullOrEmpty(xzq))
            {
                strWhere += " and (xzq like '%" + xzq + "%')";
            }
            if (!string.IsNullOrEmpty(qaakey))
            {
                strWhere += " and (number like '%" + qaakey + "%')";
            }

            if (!string.IsNullOrEmpty(txtKeyWord))
            {
                strWhere += " and (hzxm like '%" + txtKeyWord + "%' OR fwbh like '%" + txtKeyWord + "%')";
            }
            if (string.IsNullOrEmpty(startTime) && string.IsNullOrEmpty(endTime))
            {
                startTime = DateTime.Now.ToShortDateString();
                endTime = DateTime.Now.ToShortDateString();
            }

            string sql = "select ROW_NUMBER() OVER ()index,* from (select mm.*,nn.number from  (select t1.xzqdm, t1.xzq,t2.town towndm,t2.village,t1.hzxm, t1.fwbh, address,case when  t1.fwzt is null  then '正常出租' else case when t1.fwzt='1' then '不出租' else '已拆除' end  end fwzt,t2.create_by,to_char(t2.create_on::timestamp(0) without time zone, 'YYYY-MM-DD HH24:MI:SS')   pcsj from zm_pc_base_cj t1,zm_pc_yh_cj t2 where t1.id=t2.base_id and t1.fwzt is null ) mm left join zm_user_cj nn on mm.create_by=nn.\"number\" order by village  )  t  " +
                " where 1 = 1   " + strWhere + "  limit " + pagesize + " offset (" + pageindex + " - 1) * " + pagesize + "";
            if (sql.Contains("insert    into", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            string sql_count = "select count(1) cnt from (select mm.*,nn.number from  (select t1.xzqdm, t1.xzq,t2.town towndm,t2.village,t1.hzxm, t1.fwbh, address,case when  t1.fwzt is null  then '正常出租' else case when t1.fwzt='1' then '不出租' else '已拆除' end  end fwzt,t2.create_by,to_char(t2.create_on::timestamp(0) without time zone, 'YYYY-MM-DD HH24:MI:SS')   pcsj from zm_pc_base_cj t1,zm_pc_yh_cj t2 where t1.id=t2.base_id and t1.fwzt is null ) mm left join zm_user_cj nn on mm.create_by=nn.\"number\"  ) t where 1 = 1   " + strWhere + " ";
            if (sql_count.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }


            int count = _dbContext.Database.GetDbConnection().QueryFirstOrDefault<int>(sql_count);
            var datas = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            return new RespRet<Page<object>>(true, "ok", new Page<object> { Items = datas, NextPageIndex = pageindex + 1, PageTotal = count, Total = count });

        }


        /// <summary>
        /// 获取当前区某个核查人员所有已核查的房屋
        /// </summary>
        /// <param name="pageindex"></param>
        /// <param name="pagesize"></param>
        /// <param name="txtKeyWord"></param>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <returns></returns>
        [HttpGet("village/evolvestatic/getUserHcHourseList")]
        public RespRet<Page<object>> getUserHcHourseList([FromQuery] int pageindex, [FromQuery] int pagesize, [FromQuery] string xzq, [FromQuery] string txtKeyWord, [FromQuery] string startTime, [FromQuery] string endTime, [FromQuery] string qaakey)
        {
            if (pageindex < 0)
            {
                return new RespRet<Page<object>>(false, "PageIndex must >= 0");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }
            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<Page<object>>(false, "用户名不存在");
            }
            string district = user.District;

            var options = new JsonSerializerOptions();
            options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
            Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);

            string strWhere = "  ";
            if (user.RoleType == 3)  //区级
            {
                strWhere += " and (xzqdm ='" + model.RegionCode + "')";
            }
            else if (user.RoleType == 4)
            {
                string tdm = model.RegionCode;
                if (model.RegionCode.Length != 12)
                {
                    tdm = model.RegionCode + "000";
                }
                strWhere += " and towndm='" + tdm + "'";
            }
            else if (user.RoleType == 5)
            {
                strWhere += " and (villagedm::text like '%" + model.RegionCode + "%')";
            }

            if (!string.IsNullOrEmpty(xzq))
            {
                strWhere += " and (xzq like '%" + xzq + "%')";
            }
            if (!string.IsNullOrEmpty(qaakey))
            {
                strWhere += " and (number like '%" + qaakey + "%')";
            }

            if (!string.IsNullOrEmpty(txtKeyWord))
            {
                strWhere += " and (hzxm like '%" + txtKeyWord + "%' OR fwbh like '%" + txtKeyWord + "%')";
            }
            if (string.IsNullOrEmpty(startTime) && string.IsNullOrEmpty(endTime))
            {
                startTime = DateTime.Now.ToShortDateString();
                endTime = DateTime.Now.ToShortDateString();
            }

            string sql = "select ROW_NUMBER() OVER ()index,* from (select mm.*,nn.number from  (select t1.xzq,t2.town,t2.village,t1.hzxm, t1.fwbh, address,case when  t1.fwzt is null  then '正常出租' else case when t1.fwzt='1' then '不出租' else '已拆除' end  end fwzt,t2.create_by,to_char(t2.create_on::timestamp(0) without time zone, 'YYYY-MM-DD HH24:MI:SS')   pcsj from zm_pc_base_sqjz t1,zm_pc_yh_sqjz t2 where t1.id=t2.base_id and t1.fwzt is null ) mm left join zm_user_cj nn on mm.create_by=nn.\"number\" order by village  )  t  " +
                " where 1 = 1   " + strWhere + "  limit " + pagesize + " offset (" + pageindex + " - 1) * " + pagesize + "";
            if (sql.Contains("insert    into", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            string sql_count = "select count(1) cnt from (select mm.*,nn.number from  (select t1.xzq,t2.town,t2.village,t1.hzxm, t1.fwbh, address,case when  t1.fwzt is null  then '正常出租' else case when t1.fwzt='1' then '不出租' else '已拆除' end  end fwzt,t2.create_by,to_char(t2.create_on::timestamp(0) without time zone, 'YYYY-MM-DD HH24:MI:SS')   pcsj from zm_pc_base_sqjz t1,zm_pc_yh_sqjz t2 where t1.id=t2.base_id and t1.fwzt is null ) mm left join zm_user_cj nn on mm.create_by=nn.\"number\"  ) t where 1 = 1   " + strWhere + " ";
            if (sql_count.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }


            int count = _dbContext.Database.GetDbConnection().QueryFirstOrDefault<int>(sql_count);
            var datas = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            return new RespRet<Page<object>>(true, "ok", new Page<object> { Items = datas, NextPageIndex = pageindex + 1, PageTotal = count, Total = count });




        }


    }

}




